Results 1 to 5 of 5

20131107, 22:48 #1
 Join Date
 Jun 2005
 Posts
 406
 Thanks
 3
 Thanked 0 Times in 0 Posts
finding duplicates in a data base with 3 three criteria points
I have a Excel database of customer and service dates and service codes. Althought there may be multiple types of services for each day for each customer, NO ONE service can be given more than once for each customer per day.
Is there a formula which will identify the same service recorded more than once in a day for a customer?
I attached an example of the data base with some multiple duplicates, an individual service code recorded on the same day multiple times. I annotated the duplicate items the way I want the formula to work.
Thank you in advance

20131108, 03:06 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
In D2:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"",IF(COUNTIF S(A$1:A2,A2,B$1:B2,B2,C$1:C2,C2)=1,"ORIGINAL","DUP LICATE"))
Copy it down the column. It does not completely match yours, I think you missed some and mislabeled others...
Steve

20131108, 06:06 #3
 Join Date
 Jun 2005
 Posts
 406
 Thanks
 3
 Thanked 0 Times in 0 Posts
That works. Thank you.
I have two questions after thinking some more about the model.
1. How does this formula work. I went back to evaluate the formula and I did not understand the logic, is a quick primer possible.
2. For the formula itself, could it tell what number of the duplicate item it is? For instance in row 22  26 there is 1original and 3duplicates. could it report in the same column or the next column to the right it is duplicate 1 or duplicate 2 or duplicate 3?
Thank you

20131108, 08:30 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
For how the formula works. Lets starth wit the pieces:
COUNTIFS(A:A,A2,B:B,B2,C:C,C2)
Countifs can use multiple criteria, this one uses 3 and counts the number of items where all 3 criteria are met: A2 is in Col A, B2 is in Col B, C2 is in Col C
This gives the total number of items that match the 3 criteria. If the number is =1, the item is unique in the list so therefore, make it look blank with a null string:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"",...
Now we need to differentiate if it is original or a duplicate:
The formula segment
COUNTIFS(A$1:A2,A2,B$1:B2,B2,C$1:C2,C2)
is "locked" (with the $1) in the header row but as it copies down the 2nd part of each range expands so the formula is ONLY looking at the current row and the rows above[eg in row 20 the formula will be:COUNTIFS(A$1:A20,A20,B$1:B20,B20,C$1:C20,C20)]
This looks at the number matching the same 3 criteria we used earlier, but does not look at the whole column only from the start of the list to the current row. Therefore the "original" will have a count of 1 (itself), the 1st dup a count of 2 (itself and the original in some row above), the 2nd dup will have a count of 3 (itself, Dup1 and original), etc.
So if this portion is = 1 it is the original, if not = 1 there must be duplicates which gave my original response:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"",IF(COUNTIF S(A$1:A2,A2,B$1:B2,B2,C$1:C2,C2)=1,"ORIGINAL","DUP LICATE"))
For the 2nd part of your question:
Since the number of the duplicate is 1 less than the total number of copies from the start to the current row, we can reuse the 2nd countifs and subtract 1 from it giving:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"",IF(COUNTIF S(A$1:A2,A2,B$1:B2,B2,C$1:C2,C2)=1,"ORIGINAL","DUP LICATE"&COUNTIFS(A$1:A2,A2,B$1:B2,B2,C$1:C2,C2)1))
Hope this helps. Let me know if you need additional clarification.
Steve

20131108, 18:35 #5
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 3,038
 Thanks
 166
 Thanked 800 Times in 729 Posts
How about a user defined function?
Paste the code in a standard module and then enter the formula into the cell as you would any standard Excel formula.
Enter:
=ServiceCodes(C2)
in Cell D2 then copy down.
HTH,
Maud
ServiceCodes1.png
Code:Public Function ServiceCodes(cell As Range) As String Application.Volatile 'SET VARIABLES Lastrow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row celldate = cell.Offset(0, 1).Value code = cell.Value DupNum = 1 result = "Original" ' 'CHECK FOR DUPS For I = 2 To cell.Row  1 If Cells(I, 2) = celldate And Cells(I, 3) = code Then result = "Duplicate " & DupNum DupNum = DupNum + 1 End If Next I ServiceCodes = result End Function