1. ## Finding Duplicate Entries

I have a work sheet with about 15,000 rows. I have a number of rows that I would like to get rid of that would bring the number down to around 9,000. Based on the contents of columns A,B,& D being identical, I would like to delete all but 1 instance of this row. Is there a way to do this automatically or would I need to write a scipt to do this? (Example - row 1, row 123 and row 3,040 have identical info in A,B & D - delete 2 of them and leave 1, but the sheet will contain other rows with identical info in these columns but not the same as the first ones, and so on...if that makes any sense.)
I would appreciate any help with this.
[img]/w3timages/icons/alien.gif[/img]

2. ## Re: Finding Duplicate Entries

Two quick methods come to mind:
Sort your data by those three columns and do a manual inspection / deletion - OR - use the Advanced Filter (Data / Filter / Advanced Filter) to filter the list to unique values then copy this list to a new area.

3. ## Re: Finding Duplicate Entries

Gene, a manual inspection of 15000 rows? Yuk!

Do a sort on the three columns as you suggest, then (assuming row 1 contains headers) in cell E2 put the formula:
=IF(AND(A2=A1,B2=B1,D2=D1),1,0). Next fill down to the end of the data, then replace the results with values only. The first occurence of duplicate entries now has a 0 in column E, the other copies have a 1. Sort on column E and delete the entries with 1 in column E.

4. ## Re: Finding Duplicate Entries

Yuk! -- I agree...
That's why I like the 'Advanced Filter' approach. I've also used variations of your suggestion, but my brain was in 1/3 speed when I posted my response.

5. ## Re: Finding Duplicate Entries

Yep I should have remembered that, duplicate entries came up in the old Lounge and Chip Pearson's site was recommended.

Gosh is this new Lounge active, by the time I'd read through the newbies on offer there were two emails responding to my suggestion. Do you guys do anything but monitor the Lounge?

6. ## Re: Finding Duplicate Entries

THIS IS BRILLIANT! Much better than advanced filter solutions, which is the way I've always taught it. So simple it hits you in the nose! THANK YOU.

7. ## Re: Finding Duplicate Entries

Pleased you like it. Don't forget Legare's great tip that double clicking on the fill handle automatically fills down until there is a blank cell in the column to the left.

8. ## Re: Finding Duplicate Entries

Consider using these tricks:
<A target="_blank" HREF=http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique>http://www.cpearson.com/excel/duplic...tractingUnique</A>