Formula to Determine REPEAT Offenders

[JustBrowsing305]

Good Morning, First off any help is greatly appreciated. I've run into a formula that I can't figure out. My boss wants to see which of our employees are "repeat" offenders in a spreadsheet. To put it simple. I need a formula that recognizes a "repeat" UNDER THE CONDITION that THAT same employee ID is on another date. The issue is that ONE unique employee ID might come out multiple times in one day simply because they might have multiple offenses in one day. The way that the data is pulled from the report is: We have the Dates of the offense on the "A" column. We have the employee ID on the "B" column. Then column "C-G" (Row 1) we have the dates that we want to check if these employee committed an offense on TWO or more separate dates. I can't just use a formula that counts the employee ID because if an employee commits a lot of errors/offenses in one day it doesn't matter. What matters is if this is a repeated offender who needs to be properly trained on the procedure. Any help is greatly appreciated. Thank you in advance. Below is a super simplified version what I'm asking but it's basically the dilemma I'm facing.

[TABLE="width: 491"]
<tbody>[TR]
[TD]Date:[/TD]
[TD]Employee ID:[/TD]
[TD]1/8/2018[/TD]
[TD]1/9/2018[/TD]
[TD]1/10/2018[/TD]
[TD]1/11/2018[/TD]
[TD]1/12/2018[/TD]
[/TR]
[TR]
[TD]1/8/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/8/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/8/2018[/TD]
[TD]34E[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/9/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/10/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/10/2108[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/11/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1/12/2018[/TD]
[TD]41K[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col span="2"><col span="3"></colgroup>[/TABLE]

 

[snjpverma]

the quickest solution would be doing a PIVOT table based on Employee ID in row field.

 

[JustBrowsing305]

the quickest solution would be doing a PIVOT table based on Employee ID in row field.

Thank you for your reply. And my boss and I have tried that but through the entire state we have over 1200 employees. The example i put there is the very simplified version of it. When we put the employee ID in the pivot table, the table row maximum is 1000. And even when we add slicers to divide it by divisions, the data still displays single offenders. My boss wants to see only repeat offenders.

 

[JustBrowsing305]

Any help is greatly appreciated. My boss needs this by today and I still haven't figured out how to do it.

 

[Tetra201]

For clarity, could you please populate the table from Post # 1 with the expected results?

 

[JustBrowsing305]

Actually,
I managed to figure it out. The only issue is that these formulas causes the processors on the computer to go crazy (then again the file that I'm working on is ridiculously huge). But hopefully this helps someone in the future. The formula that I created (I'm referring it to the table that I used as an example) is =IF(COUNTIFS($B:$B,$B2,$A:$A,C$1)=0,"",COUNTIFS($B:$B,$B2,$A:$A,C$1)). What is this formula doing? This formula is counting how many times the employee ID comes out under the Criteria that the date (either C1,D1,E1,F1,G1) on the row of that employee ID matches C1. So, in this scenario it counts that for 1/8/18 there was 2 errors by employee ID 41K. But my boss doesn't care if they committed multiple errors in ONE day, he only cares if they commit errors on more than one day. So, that's why I put an "if" formula so that if COUNTIFS($B:$B,$B2,$A:$A,C$1) results in 0, leave it blank. On the column H, I added the following formula: IF(COUNT(C2:G2)>=2,"Yes","No"). So as to if that employee ID has a DATE count of 2 or equal to 2, then "yes" he's a repeated offender. I hope this helps someone, I'm not a great teacher like Mr.Excel but hope it helps.

 

[jtakw]

Actually,
I managed to figure it out. The only issue is that these formulas causes the processors on the computer to go crazy (then again the file that I'm working on is ridiculously huge). But hopefully this helps someone in the future. The formula that I created (I'm referring it to the table that I used as an example) is =IF(COUNTIFS($B:$B,$B2,$A:$A,C$1)=0,"",COUNTIFS($B:$B,$B2,$A:$A,C$1)). What is this formula doing? This formula is counting how many times the employee ID comes out under the Criteria that the date (either C1,D1,E1,F1,G1) on the row of that employee ID matches C1. So, in this scenario it counts that for 1/8/18 there was 2 errors by employee ID 41K. But my boss doesn't care if they committed multiple errors in ONE day, he only cares if they commit errors on more than one day. So, that's why I put an "if" formula so that if COUNTIFS($B:$B,$B2,$A:$A,C$1) results in 0, leave it blank. On the column H, I added the following formula: IF(COUNT(C2:G2)>=2,"Yes","No"). So as to if that employee ID has a DATE count of 2 or equal to 2, then "yes" he's a repeated offender. I hope this helps someone, I'm not a great teacher like Mr.Excel but hope it helps.

Hi,

You can take out the first part of the formula you're using, and modify the second part to eliminate showing the "double" count:

Excel 2010
	A	B	C	D	E	F	G
1	Date:	Employee ID:	1/8/2018	1/9/2018	1/10/2018	1/11/2018	1/12/2018
2	1/8/2018	41K	1	1	1	1	1
3	1/8/2018	41K	1	1	1	1	1
4	1/8/2018	34E	1
5	1/9/2018	41K	1	1	1	1	1
6	1/10/2018	41K	1	1	1	1	1
7	1/10/2108	41K	1	1	1	1	1
8	1/11/2018	41K	1	1	1	1	1
9	1/12/2018	41K	1	1	1	1	1
Sheet4

Cell Formulas
Range	Formula
C2	=IF(COUNTIFS($B:$B,$B2,$A:$A,C$1)>=1,1,"")

 

[JustBrowsing305]

You are the best, thank you for your reply and for your help.

 

[jtakw]

You're welcome, welcome to the forum.BTW, to speed up the formula/calculations, you probably don't want to use entire Column references (there are over 1 Million rows in Excel), so may be limit it to 3000 rows or whatever number that you are certain your data won't exceed, since you say you have over 1200 employees, something like:

=IF(COUNTIFS($B$2:$B$3000,$B2,$A$2:$A$3000,C$1)>=1,1,"")

 

[JustBrowsing305]

I will implement that formula into the Spreadsheet. The formula/calculations were taking a ridiculous amount of time to run so that will definitely save time. Thank you so much