Finding Duplicate Entries

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Consider using these tricks:
http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique
Edited to create hyperlink

Reply | Quote

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?

Reply | Quote