Excel - Comparing 2 columns and removing duplicates

December 2016




Issue


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 
1 
2 4 
1 6 
1 
3 
4 2 



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

Solution


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))
    
    Rows("1:1").Select
    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

Note


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.