Excel Comparing 2 columns removing duplicates Solved/Closed

Question

Hi,

I would like to cmpare 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?

Thanks.

rizvisa1 · Answer

Try this


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

Excel Comparing 2 columns removing duplicates

1 response

Subscribe To Our Newsletter!