Excel 2007 - Duplicate Data Removal Issue

April 16, 2008 11:26 AM

Excel 2007 - Is there a way to remove ONLY a duplicate row leaving the first unique entry?

I have a list of sweepstakes entries that need to be pared down to only unique entries. When I attempt to remove duplicate data it usually removes both the duplicates AND the first entry. I need to find a simple way (without formulas, if possible) to separate the duplicates from the unique entries.

The data columns are as follows: Entry Date, First Name, Last Name, Email, Birthdate, Address, City, State, Zip, Sex, Phone Number, Opt In 1, Opt In 2.

I need to remove all duplicate email addresses from the list. There are a few issues with the way I currently do things. If I just highlight the email column and remove duplicate data it shifts all the unique addresses up so now the data is out of alignment. If I select the entire spreadsheet and remove duplicates it doesn't come up with the same number of duplicate entries as I get with just the email column. Finally, if I apply conditional formatting to the duplicate entries it highlights all versions of the same email address. Thus, I would be removing all entires for that person. I need to allow them one and remove the rest.

I realize there are ways to do this with formulas but I have to do this often and applying a formula to tens of thousands of rows is very, very cumbersome.

Oh Hive Mind, please help me!

posted by Sandor Clegane to computers & internet (5 comments total)

Try this article.
posted by owhydididoit at 11:31 AM on April 16, 2008

Or how about selecting the text, Data / Advanced Filter and selecting Only Unique Records?
posted by jontyjago at 12:24 PM on April 16, 2008

Sorry, that should be Data / Filter / Advanced Filter...

I usually put it in new cells as well, but it's probably not necessary.
posted by jontyjago at 12:25 PM on April 16, 2008

Just recently I used Duplicate Master, with great success, when the above filter method wasn't doing what I needed.
posted by TuxHeDoh at 12:29 PM on April 16, 2008 [1 favorite has favorites]

TuxHeDoh -

That works great! It not only marks duplicates but distinguishes between the first entry and all subsequent entries. Thanks!
posted by Sandor Clegane at 12:42 PM on April 18, 2008