Remove Duplicates in Excel

by Gregory on July 15, 2010

Remove Duplicates Confirmation Popup

I use an iPhone app to record my food intake and exercise output everyday, and also record my weight, albeit infrequently. All of this data is synced to a database in the cloud.

The database automatically enters my weight each day, even though I don’t, by using the last known data point. All of this data can be downloaded in a CSV file, opened with Excel, and saved as a workbook file.

Charting my weight from this data is a simple matter, but Excel doesn’t need all of the extra data points. Consequently, I have reason to use the Remove Duplicates feature that was introduced in Excel 2007.

Using Dates with the Remove Duplicates Feature

I have two columns of data with Date in column A and Weight in column B. I want to remove all duplicate Weights, but have to be careful because it makes a difference how the dates are sorted.

The file downloaded from the database was sorted in descending order by Date. When using Remove Duplicates I got a different result when the Dates were sorted in ascending order. In each case, the same number of unique Weight values were found, but associated with different Dates.

Apparently the Remove Duplicates works from the top down so sorting dates in ascending order makes sense. Keep that in mind when Date values are part of your data set.

Here is an example of the raw data, on the left, and the results from using Remove Duplicates when the data was sorted in descending verses ascending order.

Remove Duplicates Sort by Date

Steps to Removing Duplicate Data in Excel

Select the data range or make sure the active cell is inside the data range you want to manipulate. Excel is smart enough to pick out the region of data and figure out if there are column headers.

First thing, make sure the data is sorted. I selected cell B2 and sorted the range in ascending order so the first unique Weight value would correspond to the first Date, and not the last.

  • Select Data tab » Remove Duplicates, which will bring up a dialog box.

Remove Duplicates Dialog Box

  • Select the column(s) that have duplicate data
  • Check an see if the My data has headers box is checked (assuming you have column headers)
  • Click OK

A popup box will confirm the number of duplicate and unique values

Remove Duplicates Confirmation Popup

If your not satisfied with the result, use the keyboard shortcut Ctrl+Z to undo the Remove Duplicates action.

Related Posts Plugin for WordPress, Blogger...

Previous post:

Next post: