Excel - Comparing 2 columns and removing duplicates

May 2017


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.


Published by aakai1056. Latest update on May 10, 2010 at 07:37 AM by aakai1056.
This document, titled "Excel - Comparing 2 columns and removing duplicates," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).