Ask a question Report

Compare two columns and delete duplicates [Solved]

tmiddlemas - Latest answer on Mar 17, 2010 3:07pm GMT

I have a large amount of data that is supposed to be unique based on two columns (Column A, Column B). The combination of these two columns are not supposed to contain any duplicates. Columns C - Q can contain duplicates. What I need is a macro to run that will find any duplicates based on the values in Column A and Column B and if found, delete the duplicate row.
Example:
Column A Column B Column C Column 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
I would need it to identify and delete the second row of 00002, AAAA1, yyyy, xxxx and leave all other rows alone as none are true duplicates based on comparing Column A and Column B.
The data this macro needs to compare is sorted by Column A, then Column B. There are over 14,000 rows in the spreadsheet. Any ideas?

Answer

venkat1926 1486Posts Sunday June 14, 2009Registration date ContributorStatus May 1, 2013Last seen Mar 17, 2010 1:16am GMT

This is a hybrid soltuion-sheet solution and macro

Your are messing the data . so KEEP THE DATA SAFELY SOME WHERE FOR EASY RETRIEVAL.Iin this sample workbook you copy the data in another sheet e.g. sheet 3

row 1 is having some headings
the sample data is like this:

hdng1 hdng2 hdng3 hdng4
00001 AAAA1 xxxx yyyy
00001 BBBB1 xxxx xxxx
00001 BBBB2 yyyy yyyy
00002 AAAA1 yyyy xxxx
00002 AAAA1 yyyy xxxx
00003 AAAA1 xxxx yyyy

In E2 type this formula(E1 is having some heading)
=A2&B2
copy E2 down. easy way of copying down large no of rows is take the cursor to the right bottom of the cell E2. The cursor turns into sign +. click this + sign. E2 will be copied down the rows in column E

now run this macro given below.
once the macro is run the sixth row will be deleted. to test for a rerun of the macro delete data in sheet 1 and copy it in from sheet3 and again run the macro

In the original file also you can copy the data in sheet 1 in sheet 3 also in addition to SAVING THE DATA SOMEWHERE ELSE SAFELY.

THE MACRO IS

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

Finally after running the macro and your job is over you can delete the column E if necessary.

Add comment

tmiddlemas - Mar 17, 2010 3:07pm GMT

Thank you, Venkat! That worked. I appreciate your quick response.

Reply to this topic

Ask a question

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Compare two columns and delete duplicates [Solved]

Not a member yet? sign-up, it takes less than a minute and it's free!

Not a member yet?

sign-up, it takes less than a minute and it's free!