Imposible matching

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
629
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,005
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1Date & TimeClient IDUnknownAgent IDWithin 10 Mins
24/12/2021 7:30AB123456YZ1234563
34/12/2021 7:35AB123456YZ1234564
44/12/2021 7:37AB123456YZ1234564
54/12/2021 7:41AB123456YZ1234563
64/12/2021 7:41CD123456UV1234561
74/12/2021 7:41CD123456YZ1234561
8
Sheet1
Cell Formulas
RangeFormula
E2:E7E2=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

Active Member
Joined
Jan 13, 2021
Messages
320
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Excel 365?

MrExcelPlayground.xlsm
BCDEFGHI
1DateClientSillyAgent2 or more Records in within 10minsAgent# Times agent doesn't have two records for a client
24/19/2021 9:57AA1234AA99991AA99991
34/19/2021 10:01BB1234BB99991BB99990
44/19/2021 10:05AA1234AA99991CC99991
54/19/2021 10:09CC1234CC99990DD99991
64/19/2021 10:09BB1234BB99991EE99990
74/19/2021 10:17DD1234DD99991FF99990
84/19/2021 10:21DD1234DD99991
94/19/2021 10:25DD1234DD99991
104/19/2021 10:29EE1234EE99990
114/19/2021 10:33FF1234FF99991
124/19/2021 10:37FF1234FF99991
134/19/2021 10:41FF1235AA99991
144/19/2021 10:45FF1235AA99991
154/19/2021 10:49FF1237BB99991
164/19/2021 10:53FF1237BB99991
174/19/2021 10:57FF1239CC99991
184/19/2021 11:01FF1239CC99991
194/19/2021 11:05FF1242EE99991
204/19/2021 11:09FF1241DD99990
214/19/2021 11:13FF1242EE99991
224/19/2021 11:17FF1244BB99991
234/19/2021 11:21FF1244BB99991
244/19/2021 11:25FF1246FF99991
254/19/2021 11:29FF1246FF99991
264/19/2021 11:33FF1248AA99990
Sheet52
Cell Formulas
RangeFormula
I2:I7I2=COUNTIFS($E$2:$E$26,H2,F2:F26,0)
F2:F26F2=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)
 

Forum statistics

Threads
1,143,637
Messages
5,719,972
Members
422,253
Latest member
frankie2016tata

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top