Deleting rows based on duplicates in one column only

**KD70** · 10-12-2012, 11:21 AM

Not sure if that explains it.

I have multiple entries for each Id no (column AL) and I want to identify and remove all duplicates. Even though there are some variants as you go across the row, it doesn't matter if the whole row gets deleted (I don't need to merge the rows). In the example attached, I should end up with four rows. Is there a simple(ish) way of doing this? Please bear in mind, I can cut and paste like a genius but have little understanding of why all these fixes work!

Thanks

K

**ChemistB** · 10-12-2012, 11:28 AM

In AM2 dragged down
=COUNTIF($AL$2:AL2, AL2)
This counts how many times each value in AL appears (as they appear)
Then autofilter on Column AM any value greater than 1 and delete those rows
Does that work for you?

**mike7952** · 10-12-2012, 11:31 AM

If you have 2007 or greater Excel has a built in remove dupes. Check out this link

http://blog.contextures.com/archives...in-excel-2007/

**ChemistB** · 10-12-2012, 11:32 AM

Sheesh, I didn't even think of that Mike. Nice call.

**mike7952** · 10-12-2012, 11:45 AM

Thanks B, Sometimes I have a plan.

**reachharry** · 10-12-2012, 12:53 PM

Hi,

Remove duplicates would not work in this case to remove the duplicates because your subject column has different entries for the same ID which wont let you to remove the duplicates.

Please find attached the solution file, i developed a small macro which will take care of this problem for you. When you open this file, enable the macro and click on the button provided in the file.

Hope this helps...

If you are happy with the solution, click on the left hand bottom corner "star" icon.

Thanks,
Hariharan

**mike7952** · 10-12-2012, 12:58 PM

@reachharry

Have you even tried the Built in excel remove dupes function? If so and you did not get the correct results, then follow these steps.

0. Click on remove dupes
1. Unselect all
2. Select the last row in the Listbox.
3 Click remove dupes.

Works for me