Retrieving unique values from a column or list in Excel 2007

To generate a list of unique values from a column/list in excel with repeating values it is possible to use the “Advanced Filtering” functionality. To demonstrate let us look at an example where you have some de-normalised data such as a list of countries and colours on their flag you can retrieve both a list of the countries and a list of unique colours used.

Here is a list of our initial data:

Figure 1) Sample Denormalised Data

From this we want to return a list of (a) unique countries & (b) unique colours used.

1) In the menu click on Data and then in the Ribbon click on “Advanced” in the filter section.

Figure 2) Advanced Filter Button

2) If you get a warning about not being able to determine which row contains column labels click on OK (this will assume the first row in your data selection is the column header).

Figure 3) Column Label Warning

3) In the advanced filter dialog:

• Select “Copy to another location”.
• In the ‘List Range’ select a column of data (eg. the country column).
• Leave criteria range blank.
• In the ‘Copy To’ select a single cell in the same sheet to copy the data to.
• Ensure the “Unique records only” checkbox is ticked.
• Click OK

Figure 4) Advanced Filter Dialog

4) Repeat for the ‘Colour’ column.

Figure 5) Filter Colour Column

5) You should now have a unique list of countries & colours as follows (note the column headers for the unique lists was changed).

Figure 6) Final Result