Excel - Comparing 2 columns and removing duplicates

Ask a question


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.

Excel - Copy data next to a cell and paste it below
Excel - How to insert rows when criteria is met?