Thread: finding duplicates in a data base with 3 three criteria points

1. 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

2. 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

3. 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 1-original and 3-duplicates. 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

4. 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

5. 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```
Even picked up a duplicate you missed!

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•