Find the Unique Values in a Column

September 14, 2022 - by Bill Jelen

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: Below, learn how you could use the older Advanced Filter to solve the problem. Follow these steps:

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

Preparing to use the Advanced Filter. Copy the Customer heading from D1 to a blank area of the worksheet: H1. — Figure 685. 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.

In the Advanced Filter dialog, change the Action to Copy to Another Location. The List Range is A1:F567. The Copy to location is the word Customer in H1. Choose the box for Unique Records Only, then click OK. — Figure 686. 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.

The unique list of customers is not sorted> Walmart, GM, Ford, Ainsworth, Chevron, Verizon, and so on. — Figure 687. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Pablo Hermoso on Unsplash