Excel - Comparing 2 columns and removing duplicates

October 2016


I would like to compare 2 columns in an Excel spreadsheet & remove duplicates from Column B if any matching fields are found e.g.

A B 
1 1 
2 4 
1 6 
1 1 
3 3 
4 2 

So after a formula/macro is run, then the following should be the result:

A B 
2 4 
1 6 
4 2 

Would someone be kind enough to help me with this please?


Please use the following code:

Sub ClearSameValue()
Dim lMaxRows As Long
Dim iTempCol As Integer

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    iTempCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    Cells(1, iTempCol) = "Temp Value"
    Cells(2, iTempCol).Select
    ActiveCell.Formula = "=IF(A2=B2,1,0)"
    Selection.AutoFill Destination:=Range(Cells(2, iTempCol), Cells(lMaxRows, iTempCol))
    If (ActiveSheet.AutoFilterMode = False) Then Selection.AutoFilter
    Selection.AutoFilter Field:=iTempCol, Criteria1:="=1"
    Range(Cells(2, "B"), Cells(lMaxRows, "B")).ClearContents
    ActiveSheet.AutoFilterMode = False
    Range(Cells(1, iTempCol), Cells(lMaxRows, iTempCol)).ClearContents
End Sub


Thanks to rizvisa1 for this tip on the forum.

Related :

This document entitled « Excel - Comparing 2 columns and removing duplicates » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.