Clear duplicates between two columns

Pcwork2006

New Member
Joined
Jul 17, 2019
Messages
12
Good Days,
Please advise a vba code to process the duplicates values clean task,

If word/text in Cell A1 is equal to Cell B2 ,then Cell B2.clearcontents

......

If word/text in Cell A65536 is equal to Cell B65536, then Cell B65536.clearcontents

Else, no change

Thank you very much!
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try this:
Code:
Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
    If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,

how can we delete entire row instead of copying value,

also can we compare Col 1 & Coloumn 2
Coloumn 2 & Coloumn 3 also with this formula

Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub
 
Last edited:
Upvote 0
This is not your question. This question was asked by the original poster. If you have a question it's best to start a new posting.
Hi,

how can we delete entire row instead of copying value,

also can we compare Col 1 & Coloumn 2
Coloumn 2 & Coloumn 3 also with this formula

Sub Clear_Me()
'Modified 8/26/2019 2:49:54 AM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 1).Value = Cells(i, 2).Value Then Cells(i, 2).Value = ""
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Probably faster without using a loop :

Uses column CX (COL# 102) as temporary helper column ... If the worksheet is protected then the code should be slightly amended as required.

Code:
Option Explicit

Sub Test()

    Dim oRange As Range
    Dim lCalculationMode As Long
    
    On Error GoTo errHandler
    
    lCalculationMode = Application.Calculation
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    
    Set oRange = Columns("A:B").RowDifferences(Range("A1"))
    oRange.Offset(, 100).Value = "~#@" [B][COLOR=#008000] '<=== Use column CX as a temporary help column[/COLOR][/B]
    Set oRange = oRange.Offset(, 100).EntireColumn.ColumnDifferences(oRange.Offset(, 100).Cells(1).End(xlDown))
    oRange.Offset(, -100).ClearContents
    oRange.EntireColumn.ClearContents
    
errHandler:
    Application.Calculation = lCalculationMode
    Application.EnableEvents = True

End Sub
 
Upvote 0
Another option
Code:
Sub Pcwork2006()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@=" & .Offset(, -1).Address & ","""",@)", "@", .Address))
   End With
End Sub
 
Upvote 0
Another option
Code:
Sub Pcwork2006()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@=" & .Offset(, -1).Address & ","""",@)", "@", .Address))
   End With
End Sub

I like it but I am having a hard time trying to break down the "if(@... .Address) part so I can understand how this works.

Can you explain it for me ?

Thanks.
 
Upvote 0
The @ is just a place holder & is replaced by .Address by the Replace function, so it basically becomes
Code:
Evaluate("if(" & .Address & "=" & .Offset(, -1).Address & ",""""," & .Address & ")")
 
Upvote 0
I like using simple Vba coding which I understand and hopefully others will to. But I'm sure other ways of coding things may be a few seconds faster.
My code worked for me which was in post 2
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top