Compare two columns and delete duplicates [Solved/Closed]

tmiddlemas - Mar 16, 2010 at 07:06 PM - Latest reply:  tmiddlemas
- Mar 17, 2010 at 11:07 AM
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?
See more 

2 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Mar 16, 2010 at 09:16 PM
4
Thank you
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.

Thank you, venkat1926 4

Something to say? Add comment

CCM has helped 1801 users this month

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