Label first occurence

[SandsB]

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

 

[J.Ty.]

What about this?

Book1
	A	B	C	D	E
1	Row #	Date/Time	Client	Repeat
2	2	2019-01-02 00:00	1111	X	1/2/2019 4:23:58 PM is less than 3 days since the record in row 3
3	3	2019-01-02 00:00	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	4	2019-01-03 00:00	1111
5	5	2019-01-04 00:00	2222
6	6	2019-01-04 00:00	1111		This is a repeat but it's been less than 3 days since client 1111 in row 3
7	7	2019-01-08 00:00	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	8	2019-01-12 00:00	1111	X	Same client and there's a record within 3 days in row 9
9	9	2019-01-13 00:00	1111
10	10	2019-01-16 00:00	2222	X	1/16/2019 11:31:58 PM in row 11 is less than 3 days since the event in row 10
11	11	2019-01-17 00:00	2222
12	12	2019-01-19 00:00	3333	X	1/21/2019 5:04:51 PM in row 14 is less than 3 days since the event in row 12
13	13	2019-01-19 00:00	4444
14	14	2019-01-19 00:00	3333
Sheet1

Cell Formulas
Range	Formula
D2:D14	D2	=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.]

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]

Brilliant. I owe you a beer.

 

[J.Ty.]

Pay it forward!

J.Ty.