Label first occurence

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
603
I need to know when clients call in more than once within 3 days of their first call for an event. Clients can call in many time over the course of a month so I need to look at all calls within 3 days of each other as calls for the same event. Calls from the same client with a gap of more than 3 days will be considered different events.

Thousands of rows of data sorted in Date/time order. The Date/Time is in column A. The client's name is in column B. If the client has multiple records, I need to add an 'X' in column C on the row where he called in the first time (and only on that row) and there's at least one other record within 3 days of that first record. That same client might call in several times and I don't want the X on other rows unless there's been a gap of at least 3 days, then I'd consider the start of a new sequence of the client calling in.
EX:




Row #Date/TimeClientRepeat
21/2/19 4:15 PM1111X1/2/2019 4:23:58 PM is less than 3 days since the record in row 3
31/2/19 4:23 PM1111There's a call within 3 days of this one but row 4 is within 3 days of row 3 so - same event = don't flag it.
41/3/19 1:23 PM1111
51/4/19 6:19 PM2222
61/4/19 7:21 PM1111This is a repeat but it's been less than 3 days since client 1111 in row 3
71/8/19 8:09 PM1111Another repeat, more than 3 days since row 6 so I'd consider it a new event but there's no record within 3 days after this one.
81/12/19 10:07 PM1111XSame client and there's a record within 3 days in row 9
91/13/19 10:46 PM1111
101/16/19 11:11 PM2222X1/16/2019 11:31:58 PM in row 11 is less than 3 days since the event in row 10
111/17/19 11:31 PM2222
121/19/19 2:41 AM3333X1/21/2019 5:04:51 PM in row 14 is less than 3 days since the event in row 12
131/20/19 2:20 PM4444
141/21/19 1:04 PM3333
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
What about this?

Book1
ABCDE
1Row #Date/TimeClientRepeat
222019-01-02 00:001111X1/2/2019 4:23:58 PM is less than 3 days since the record in row 3
332019-01-02 00:001111 There's a call within 3 days of this one but row 4 is within 3 days of row 3 so - same event = don't flag it.
442019-01-03 00:001111 
552019-01-04 00:002222 
662019-01-04 00:001111 This is a repeat but it's been less than 3 days since client 1111 in row 3
772019-01-08 00:001111 Another repeat, more than 3 days since row 6 so I'd consider it a new event but there's no record within 3 days after this one.
882019-01-12 00:001111XSame client and there's a record within 3 days in row 9
992019-01-13 00:001111 
10102019-01-16 00:002222X1/16/2019 11:31:58 PM in row 11 is less than 3 days since the event in row 10
11112019-01-17 00:002222 
12122019-01-19 00:003333X1/21/2019 5:04:51 PM in row 14 is less than 3 days since the event in row 12
13132019-01-19 00:004444 
14142019-01-19 00:003333 
Sheet1
Cell Formulas
RangeFormula
D2:D14D2=IF(AND(COUNTIFS(B$1:B1,">="&(B2-3),C$1:C1,C2)=0,COUNTIFS(B3:B$10000,"<="&(B2+3),C3:C$10000,C2)>0),"X","")
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Row 10000 is a hypothetical end of your data. You can replace it by a relative reference, say 100 rows below (so B100 and C100) if you know that the span of 3 days will be for sure covered by 100 rows. This change would make formula a little bit faster to compute.

My formula counts 72 hours before and after each event. If you want it to round everything to complete days, please let me know.

J.Ty.
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,109
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Pay it forward!

J.Ty.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,260
Members
410,905
Latest member
Extjel
Top