Imposible matching

[SandsB]

I need to read a file of account records to determine when the same agent created a record for the same client within 10 minutes. Ultimately, I need to determine which agents are doing this and which are not. They should all be creating multiple records for the same patient sometimes while on the same call at the same rate. I just don't know if this is true for 1% of our calls or 20%, or which agents are doing this at 3% of their daily tickets and which are doing it at 0% or 30%.

Columns:
A = Date and time (EX: 04/12/2021 07:30)
B = Client ID of 2 letter and 6 numbers (EX: AB123456)
D = Agent ID of 2 letter and 6 numbers (EX: YZ123456)

In a typical day's file of 8,000 or so records, there could be dozens or hunderds of valid instances of an agent creating a record for someone and then creating another record for the same patient while they're still on the same call. There could even be 3 or more records created on the same call. A flag on each record like this or the % of each agent's total tickets that are these kinds of duplicates would answer my question.

There could also be cases of a second (or third, etc...) records created by different agents within 10 minutes - I'm not interested in those - If the agent is different that means there was a second call.

Anyway, to me this is one of those "if a train headed west at 78 MPH leaves New York at 6PM and a train headed east......" problems. I just can't do it.

 

[Alex Blakenburg]

I am sure someone will come up with a better solution.
My problem is what to use as a reference point. I was thinking of using a MINIFS as part of this but it suffered the same issue as this.
I am using +/- 10 minuntes on the current row time.
For your purpose anything > 1 is an issue.

20210420 Same call logged multiple times.xlsx
	A	B	C	D	E	F
1	Date & Time	Client ID	Unknown	Agent ID	Within 10 Mins
2	4/12/2021 7:30	AB123456		YZ123456	3
3	4/12/2021 7:35	AB123456		YZ123456	4
4	4/12/2021 7:37	AB123456		YZ123456	4
5	4/12/2021 7:41	AB123456		YZ123456	3
6	4/12/2021 7:41	CD123456		UV123456	1
7	4/12/2021 7:41	CD123456		YZ123456	1
8
Sheet1

Cell Formulas
Range	Formula
E2:E7	E2	=COUNTIFS($A$2:$A$20,">" & (A2-10/60/24), $A$2:$A$20,"<" & (A2+10/60/24), $B$2:$B$20,B2, $D$2:$D$20,D2)

 

[JamesCanale]

Excel 365?

MrExcelPlayground.xlsm
	B	C	D	E	F	G	H	I
1	Date	Client	Silly	Agent	2 or more Records in within 10mins		Agent	# Times agent doesn't have two records for a client
2	4/19/2021 9:57	AA1234		AA9999	1		AA9999	1
3	4/19/2021 10:01	BB1234		BB9999	1		BB9999	0
4	4/19/2021 10:05	AA1234		AA9999	1		CC9999	1
5	4/19/2021 10:09	CC1234		CC9999	0		DD9999	1
6	4/19/2021 10:09	BB1234		BB9999	1		EE9999	0
7	4/19/2021 10:17	DD1234		DD9999	1		FF9999	0
8	4/19/2021 10:21	DD1234		DD9999	1
9	4/19/2021 10:25	DD1234		DD9999	1
10	4/19/2021 10:29	EE1234		EE9999	0
11	4/19/2021 10:33	FF1234		FF9999	1
12	4/19/2021 10:37	FF1234		FF9999	1
13	4/19/2021 10:41	FF1235		AA9999	1
14	4/19/2021 10:45	FF1235		AA9999	1
15	4/19/2021 10:49	FF1237		BB9999	1
16	4/19/2021 10:53	FF1237		BB9999	1
17	4/19/2021 10:57	FF1239		CC9999	1
18	4/19/2021 11:01	FF1239		CC9999	1
19	4/19/2021 11:05	FF1242		EE9999	1
20	4/19/2021 11:09	FF1241		DD9999	0
21	4/19/2021 11:13	FF1242		EE9999	1
22	4/19/2021 11:17	FF1244		BB9999	1
23	4/19/2021 11:21	FF1244		BB9999	1
24	4/19/2021 11:25	FF1246		FF9999	1
25	4/19/2021 11:29	FF1246		FF9999	1
26	4/19/2021 11:33	FF1248		AA9999	0
Sheet52

Cell Formulas
Range	Formula
I2:I7	I2	=COUNTIFS($E$2:$E$26,H2,F2:F26,0)
F2:F26	F2	=IF(AND(COUNT(FILTER($B$2:$E$26,$C$2:$C$26&$E$2:$E$26=C2&E2,""))>1,MAX(FILTER($B$2:$E$26,$C$2:$C$26&$E$2:$E$26=C2&E2,""))-MIN(FILTER($B$2:$E$26,$C$2:$C$26&$E$2:$E$26=C2&E2,""))<=1/24/6),1,0)