Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    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

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    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 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. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 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
    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
  •