Excel - Compare two columns and delete duplicates

January 2017

Mathematical or logical operations cannot be done manually on Excel worksheets which contain large amounts of data, as it would take too long. But Excel is an efficient office software program that allows data to be automatically manipulated, through a macro or a VBA program code.To compare two columns and delete duplicates in Excel,a macro can be written to make a logical or mathematical comparison of the columns and remove the rows containing duplicate data. A well-written Excel macro can accomplish the task quickly and accurately. It is easy to compare two columns and delete duplicates in Excel.


I need a macro for an Excel spreadsheet that will compare lines based on two cells, and find any duplicates.

In this example, the first two columns - A and B - are the ones that must be unique. The macro should only flag rows that are a perfect match in both columns. So in this example, only the fifth row should be flagged up, as it is an exact match for the one above.


Col A Col B Col C Col D       
00001 AAAA1 xxxx yyyy       
00001 BBBB1 xxxx xxxx       
00001 BBBB2 yyyy yyyy       
00002 AAAA1 yyyy xxxx       
00002 AAAA1 yyyy xxxx       
00003 AAAA1 xxxx yyyy 


This solution will alter the data in your spreadsheet, so make sure you save a backup copy.

Firstly, you need to combine the data in columns A and B so they can be compared as a whole. To do this, type into an empty cell at the end of the first row of data (in this example we are using row 2 - change as necessary):

Copy this formula down for the whole spreadsheet.

Now run the macro below. In this sample code, it is assumed you wrote the above formula in column E. Amend the code as necessary.

Warning: This macro will delete rows, so it is important to make sure you have made your backup copy copy before testing it.

Sub text()      
Dim j As Integer, k As Integer, r As Range      
j = Range("E2").End(xlDown).Row      
For k = j To 2 Step -1      
MsgBox k      
Set r = Range(Cells(k, "E"), Cells(k, "E").End(xlUp))      
If WorksheetFunction.CountIf(r, Cells(k, "E")) > 1 Then      
Cells(k, "E").EntireRow. Delete      
End If      
Next k      
End Sub      


Thanks to venkat1926 for this tip on the forum.


Published by aakai1056. Latest update on June 29, 2012 at 10:07 AM by Jeff.
This document, titled "Excel - Compare two columns and delete duplicates," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).