Removing Duplicates in Excel 2008

by Gregory on July 22, 2010

Excel 2008 for Mac requires more work to remove duplicate values in a data set because of it’s lack of features. Excel 2007 and 2010 have the Remove Duplicates feature. Excel 2003 and Excel 2008 for Mac don’t.

In my last post I used a formula and the Find box to remove duplicate values in Excel 2003. However, Excel 2008 for Mac has no “Find All” button in the Find box.

Find All Button

You can’t “Find All” values of TRUE because that functionality doesn’t exist.

No Find All Option in Excel 2008

Nevertheless, I have to remove some duplicate values in the Weight column on my spreadsheet. So I used the following procedure:

Sort the spreadsheet in ascending order by Date.

In cell C2 add the formula: =B2=B1, which will be FALSE if C2 is unique, and TRUE if it’s a duplicate value to the cell above it.

Copy the formula down by double clicking the fill handle.

Remove Duplicates Formula Excel 2003

Select all formulas by making C2 the active cell, and using the keyboard shortcut Cmd+Shift+Down arrow.

Copy, and then Edit»Paste Special»Values .

Enter a label in cell C1. (I used ID)

Select cell C2 and click the Sort Ascending button on the toolbar.

Sort Ascending Icon

Scroll down column C and select the first TRUE value.

Use the keyboard combination Cmd+Shift+Down arrow to select the remaining TRUE values.

Right click on one of the TRUE cells, and select Delete… from the popup menu

In the Delete dialog box, select Entire Row and then click OK.

Sort Ascending Icon

Remove column C by right-clicking the header, and click Delete from the popup menu.

What’s left are the unique Weight values in chronological order.

Related Posts Plugin for WordPress, Blogger...
Jess August 12, 2011 at 2:39 pm

Wow! So helpful! Thanks for this brilliant workaround.

calvin September 8, 2011 at 1:15 pm

What if you have more than a few different values to sort by?

Gregory September 8, 2011 at 7:56 pm

I’m not exactly sure what you are referring to. Are the different sort values in different columns, or in the same column? Perhaps you could be a bit more precise, or send me a spreadsheet example. Thanks.

Igor December 12, 2011 at 6:30 am

Sorry, what does it mean “Copy the formula down by double clicking the fill handle”?.

Gregory December 12, 2011 at 6:38 am

When a cell is active, the bottom right corner has a little “square” that is called a “fill handle.” Your cursor will change when you hover over this fill handle. You can click and drag the fill handle down to copy a formula, or a double click will do the same thing.

Min January 12, 2012 at 7:32 am

Wow! Thank you so much! This worked beautifully and saved me soooo much time!

Gregory January 12, 2012 at 7:18 pm

Always glad to help.

Charlotte June 13, 2012 at 12:08 pm

Thank you so much, that’s just what I needed! Now, if only the TRUEs could be also color coded… This is my very first experience with formulas. (I’m a graphic designer — Spread sheets are Greek to me!)

Gregory June 14, 2012 at 10:03 pm

Conditional Formatting will give you color for the TRUE’s. 🙂

Dave January 1, 2013 at 6:13 am

Thank you! I can’t think of anything worse than paying Microsoft for an upgrade I don’t need!!!

Comments on this entry are closed.

Previous post:

Next post: