deleting all duplicate values in a spreadsheet

[bawlmer]

Hello All. This is my first post. I use Excel 2007. I have a sheet where I must delete all duplicate values. I know there is a button that will delete duplicates and leave the original values. Is there a way to not only delete the duplicate values but also the original values that were duplicated in the first place? All help will be greatly appreciated. Thanks...Steve

[mikeTRON]

You can create a column that is literally a concatenation of all the columns then highlight duplicates IN that column, filter by duplicates, delete all. Then remove that column, or hide it.


Or you can do a countif on the concatenation and delete everything ABOVE 1.


Post a sample data set and I will show you what I mean if everything I said above is unclear.

[bawlmer]

Hello. I believe I have attached my sheet. It has 505 duplicates in it. I apologize but could not comprehend your instructions. Hopefully a more visual approach may work. Thanks...Steve

[mikeTRON]

First off there are spaces in the dataset that might cause issues but you can simply select all of the data. Highlight duplicates with conditional formating. Create a filter, filter on cell color, then delete anything with the highlighted cell color.

That should work.

Otherwise you can create a formula in C1 with the following:

Please Login or Register  to view this content.

that will literally count how many times B1 occurs in the data. Then copy the range to the bottom by double clicking on the bottom right of the cell. Filter on columns B&C. Uncheck 1 in column C, delete everything with MORE than 1 from the data. remove filter. Done.

[Kaper]

Hi,
just one thing worth mentioning - before you apply either method of filtering - add a row above data and insert some kind of header there - "My Header" or whatever text will do. Otherwise Excel will treat the first value as a header and if it hapens to be duplicated - will not be removed.
In the attachment - data ready to be deleted - only 1010 duplicates (selected with conditional formatting) visible.

[Winon]

Hello bawlmer,

Please try the attached Sample Workbook.

It also takes care of empty cells in your original data set, should there be any.

Regards.

[bawlmer]

Thank you all for your help! As soon as I figure out how to do it I will mark this thread solved. Thanks again!

[Winon]

Hello bawlmer,

You are welcome, glad I could help.

Also thank you for adding to my Reputation, and for marking this thread as Solved.

Regards.