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.
Excel - Compare two columns and delete duplicates
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.
This macro will delete rows, so it is important to make sure you have made your backup copy copy before testing it.
Dim j As Integer, k As Integer, r As Range
j = Range("E2").End(xlDown).Row
For k = j To 2 Step -1
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
Thanks to venkat1926
for this tip on the forum.
Published by aakai1056
- Latest update by Jeff