Removing Duplicates in Excel II

February 22, 2008

One of the annoying tasks in previous versions of Excel was finding a unique list of invoices, customers, or products in a data set. Today, we’ll look at the various methods, including a new method in Excel 2007.

Method 1

Advanced Filter

Let's say you need a unique list of customers. Copy the Customer heading to a blank column in your worksheet. Select one cell in your data set. In Excel 97-2003, choose Data, Filter, Advanced. In Excel 2007, choose Data, Advanced Filter.

In the dialog, choose Copy to a New Location. Choose Unique Records Only. In the Copy to box, click on the cell containing the copy of the word Customer.

Method 2

COUNTIF

Use a formula such as =COUNTIF(C$1:C2,C2)=1. Copy this formula to all rows. Change the formula to values using the Copy icon then open the Paste dropdown and choose Paste Values. Sort descending by the new column. All of the records with TRUE in the new column represent unique customer names.

Method 3

Pivot Table Method

Choose a cell in the data set. In Excel 2007, use Insert - PivotTable. Click OK. Click Customer. In Excel 97-2003, use Data - Pivot Table and PivotChart Report. Click Finish. Double click the Customer field in the Pivot Table Field List.

Method 4

New in Excel 2007 - Remove Duplicates

Choose a cell in your data set. From the Data tab, choose Remove Duplicates. Click Unselect All. Select the Customer field. Click OK. You will be left with just one record per customer. You should not run this command on your original data - make a copy first!