CODEWITHSUNDEEP
excel
dlofrodloh
dlofrodloh

Reputation: 1744

How to remove both duplicates in excel

I have a list like the one in the screenshot below with two columns of data. I want to remove the duplicates in both columns. So in the example below, I need a method which detects and removes "Brian" from both column A and B.

How would I do this?

enter image description here

Upvotes: 0

Views: 2620

Answers (1)

Gary's Student
Gary's Student

Reputation: 96773

Try this tiny macro:

Sub DupRemover()
    Dim AB As Range, r As Range, cl As Collection
    Set AB = Range("A:B").Cells.SpecialCells(xlCellTypeConstants)
    Set cl = New Collection
    On Error Resume Next
    For Each r In AB
        v = r.Value
        k = CStr(v)
        cl.Add v, k
        If Err.Number > 0 Then
            Err.Number = 0
            AB.Replace What:=v, Replacement:=""
        End If
    Next r
End Sub

Upvotes: 1

Related Questions