Excel Comparing 2 columns removing duplicates [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

so, where I write this code, in order to work?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
1. Make a backup of the file
2. press ALT + F11
3. Click on isert and insert a new module
4. paste the code
5. Make sure that active sheet is the sheet on which you want this this
6. press function key "F5" to run macro