• Excel Book Excerpt

Excel Find the Unique Values in a Column

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Find the Unique Values in a Column

Problem: I have a large database. Before I can produce a report for each customer, I need to identify the complete list of unique customers.

Strategy: There are many solutions to the unique customers problem. One is to use the Advanced Filter command on the Data tab of the ribbon. Follow these steps:

1. Copy the Customer heading from D1 to a blank cell.

LE10000654.jpg

Figure 631 Copy the customer heading to an output area.

2. Select a single cell in your data range and then select Data, Advanced. The Advanced Filter dialog will appear, offering many confusing options.

3. Choose the Unique Records Only check box. Change the Action section to Copy to Another Location. Selecting this action enables the Copy To range. Place the cell pointer in the Copy To text box and touch the out-of-the-way copy of the Customer heading.

LE10000655.jpg

Figure 632 Copy unique records to the output range.

4. Click OK. Excel will find the unique customer numbers and copy them to the range you specified.

LE10000656.jpg

Figure 633 Excel produces a list of unique customers.

Gotcha: The list is not sorted. It appears in the same order that the customers appeared in the original data set.

Gotcha: Any subsequent use of the Advanced Filter command during this Excel session will remember the list range you specified in the Advanced Filter dialog box.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: