Compare two columns in excel and delete the duplicate row

0
votes

I have below data in two columns in excel:

AB  CD

EF  GH

CD  AB

GH  EF

If the values in column1 matches any cell in column2, the row should be deleted. Starting from cell A1, the duplicate value is found in B3 so 3rd row is deleted. Moving on to A2, the duplicate value is found in B4 so 4th row is deleted.

Output should be 

AB  CD

EF  GH
2
excelvba
What have you tried so far? – Léopold Houdin
I am able to highlight duplicates but in my case every entry is duplicate so can't highlight and delete. – Yusuf
Your output and explanation do not match, form your explanation the output would be nil – Ricards Porins
Why can't you highlight and delete - your comment says it highlights the duplicates correctly which in this case is all of them, but I can't see how that stops you from deleting all of them? If it's a case of it shouldn't be highlighting them all then you need to change your parameters for what constitutes a duplicate. – Darren Bartrup-Cook
You say If the values in column1 matches any cell in column2, the row should be deleted yet in your example, the output includes AB CD even though CD is in column 22 (you said any cell)... same for the the other example output. You should probably re-think your explanation. – ashleedawg

2 Answers

0
votes

Try this...

Dim firstCell as Range
Dim secondCell as Range

For Each firstCell in Intersect(ActiveSheet.UsedRange, Range("A:A"))
    For Each secondCell in Intersect(ActiveSheet.UsedRange, Range("B:B"))
        If firstCell.Value = secondCell.Value Then
            secondCell.EntireRow.Delete
        End If
    Next
Next
0
votes

I know that when I've run into these scenarios, I've always ended up kicking myself for deleting data. I always advocate TESTING data more thoroughly before I just decide to terminate something. So, even though I think the VBA approach is elegant, I would go for an equation approach first, and give myself some easy flags for a mass-deletion. Equations below, and in picture.

Illustration of how testing data would allow sort/filter action

Column C testing if this is the first occurrence of data:

=IFERROR(IF(ROW(A2)<MATCH(A2,B:B,0),"First","Duplc"),"First")

Column D, testing if this is the first occurrence of a data:

=IFERROR(IF(ROW(B2)<MATCH(B2,A:A,0),"First","Duplc"),"First")

Column E, testing for First/First, Duplicate/Duplicate, or new single first occurances of data:

=IF(AND(C2="First",D2="First"),"Keep",IF(AND(C2="Duplc",D2="Duplc"),"Delete","Review"))

Any time I would see a "Review" cell, I'd test to make sure that I'm not deleting a valuable data point. Your data may be more simple than what I'm doing here, but I've regretted the ultra-powerful mass deletion.