Excel - Comparing 2 columns and removing duplicates

March 2017




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


Published 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).