November 2016

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.

## Issue

**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.

Example:

## Solution

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.

## Note

Thanks to venkat1926 for this tip on the forum.

Related :

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.

Example:

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):

=A2&B2

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.

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.

Related :

- Excel - Compare two columns and delete duplicates
- Excel remove duplicates in two columns
- Excel compare 2 lists remove duplicates
- Compare two columns and delete duplicates (Solved)
- Excel - Comparing two columns A&B
- Excel - Comparing 2 columns and removing duplicates
- Excel - Comparing two columns
- Excel: Comparing two columns and output data (Solved)