Tag Archives: Remove Duplicates

Different Versions, Different Methods in Excel

For the past week I’ve been showing how to remove duplicate values from a data range in Excel. How you go about that task varies, depending on the version of Excel you use. My simple worksheet example used only two columns, and only one had duplicate values.

Excel 2010 and 2007

The Remove Duplicates feature in Excel 2010 and 2007 is capable of removing more than one column of duplicate values. It’s the best solution you can use for your data set. Simple, yet very powerful.

Excel 2003

For Excel 2003, and earlier versions, you have to come up with a manual solution for removing duplicate values. I shared one such method by using a formula to see if two consecutive cells were the same value. Finding either a TRUE or FALSE result allowed me to use the “Find All” option button on the Find dialog box to select all TRUE values so they could be deleted in one step.

Excel 2008

Excel 2008 for Mac doesn’t have the “Find All” button in the Find dialog box. Instead, a sort was used to put all TRUE values at the bottom of the range so their entire rows could be deleted from the data set. It felt quite rustic.

Removing Duplicates in Excel 2008

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

How to Remove Duplicates in Excel 2008

Nevertheless, even if the function does not exist, I still need to know how to remove duplicates in excel 2008. First, 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.

Removing Duplicate Values in Excel 2003

I have a worksheet with duplicate values that need to be removed. There are two columns of data, with Date in column A and Weight in column B.

The Date values are for each day and extend to the end of June. Some of the Weight values repeat as you can see in the picture. I only want to keep the first unique weight value, all duplicate values can be deleted.

Excel 2007 introduced a really neat feature, Remove Duplicates, which makes it very easy to remove any duplicate values in a range of data. Earlier versions of Excel have to rely on doing this task manually, through a variety of methods.

The following procedure worked well for this data set.

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 use the keyboard shortcut Ctrl+ Shift+Down arrow.

Copy, and Paste as Values.

Find all the TRUE values by using the keyboard shortcut Ctrl+F.

Type TRUE in the Find What box, and click Find All.

Click inside the bottom window of the Find and Replace dialog box, and use Ctrl+A to select all the values found.

Find and Replace TRUE Duplicates

Close the Find and Replace dialog box.

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

Delete Popup Box 2003

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

Delete Popup Box

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

Remove Duplicates Excel 2008 Finished

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

Download the file here.

Related Posts Plugin for WordPress, Blogger...