Removing Duplicate Values in Excel 2003

by Gregory on July 17, 2010

Duplicate Data 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...
QUoc Minh October 13, 2011 at 8:43 am

The instruction is very useful. Thanks !

Comments on this entry are closed.

{ 1 trackback }

Previous post:

Next post: