• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to remove duplicates based on two conditions?

shahin

Active Member
In the attached workbook there are two sheets: one named "RawData" and the other named "ExpectedOutput". In "Rawdata" there are few items spreading across three columns. My goal here is to remove duplicates based on their "Name" and "ID". When any "Name" and "ID" matches with another "Name" and "ID" then the latter should be kicked out. Items containing under "Score" header are just placeholders. I could have done it, if it were not for two conditions. The macro I'm pasting below can handle with a single condition as in, the "Name" based on its duplicity. However, I can't get any idea how I should write one based on two conditions as in, "Name" and "Id". Thanks for any solution.

This is my existing macro:
Code:
Sub ExtractDups()
    Dim lrow&, R&, dict As Object, curval As Variant

    lrow = Sheet2.Range("A" & Rows.Count).End(xlUp).row

    Set dict = New Scripting.Dictionary

    For R = lrow To 1 Step -1
        With Sheet2.Cells(R, 1)
            curval = .Value
            If dict.Exists(curval) Then
              .EntireRow.Delete
            Else
              dict.Add curval, 1
            End If
        End With
    Next R
End Sub

The problem can be solved very easily if I do like below but I don't want to do so. I wish to know the logic how it can be done using the way I showed above.

Code:
Sub RemoveDups()
   
    With Worksheets("Sheet2")
        Range("A1:C14").RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
    End With
   
End Sub
 

Attachments

  • Example.txt
    554 bytes · Views: 1
  • docs.xlsx
    8.9 KB · Views: 1
Last edited:

Just correct the logic for the condition of what you must compare …
But if you have a lot of rows to delete, this way is one of the slowest !
Of course RemoveDuplicates method is easier and faster …
Or the one I just show you in your previous thread just via Excel basics.
 
Back
Top