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:
- Copy the Customer heading from D1 to a blank cell.
- Copy the customer heading to an output area.
- Select a single cell in your data range and then select Data, Advanced. The Advanced Filter dialog will appear, offering many confusing options.
- 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.
- Copy unique records to the output range.
- Click OK. Excel will find the unique customer numbers and copy them to the range you specified.
- 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.