Label first occurence

SandsB

Well-known Member
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/Time Client Repeat 2 1/2/19 4:15 PM 1111 X 1/2/2019 4:23:58 PM is less than 3 days since the record in row 3 3 1/2/19 4:23 PM 1111 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. 4 1/3/19 1:23 PM 1111 5 1/4/19 6:19 PM 2222 6 1/4/19 7:21 PM 1111 This is a repeat but it's been less than 3 days since client 1111 in row 3 7 1/8/19 8:09 PM 1111 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. 8 1/12/19 10:07 PM 1111 X Same client and there's a record within 3 days in row 9 9 1/13/19 10:46 PM 1111 10 1/16/19 11:11 PM 2222 X 1/16/2019 11:31:58 PM in row 11 is less than 3 days since the event in row 10 11 1/17/19 11:31 PM 2222 12 1/19/19 2:41 AM 3333 X 1/21/2019 5:04:51 PM in row 14 is less than 3 days since the event in row 12 13 1/20/19 2:20 PM 4444 14 1/21/19 1:04 PM 3333

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

J.Ty.

Well-known Member

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
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.

SandsB

Well-known Member
Brilliant. I owe you a beer.

Pay it forward!

J.Ty.

Replies
6
Views
60
Replies
7
Views
83
Replies
3
Views
54
Replies
7
Views
87
Replies
1
Views
79