Filter a list for unique values

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

A frequent situation is where you have a dataset and you want to get a list of all the unique values within a column or across multiple columns.

Excel 2007 and 2010 does have the ‘Remove Duplicates’ feature on the Data tab of the ribbon – clicking this and selecting the column(s) which contain multiple values will then delete any rows which have duplicate values in the column(s). Note that selecting more than one column here will only delete a row where the value in all columns selected are duplicated (so for example you can obtain a unique list of combinations of values across Columns A and B).

The potential problem with this is that it actually deletes your data! If you just wanted to get a unique list of values (across one or more columns) then it is better to use the Advanced Filter to do this.

Firstly select the data range which you want to get unique values from. It is important that the Data Range contains data in consecutive rows (no blank rows in between) with headings in the first row.

To initiate the Advanced Filter functionality click on the ‘Advanced’ button in the ‘Data’ tab (under ‘Sort and Filter’). The Advanced Filter dialog box (as in the screen shot below) comes up.

Here we want to apply the following settings:

• Action: You can use either option here but it’s probably neater to use ‘Copy to Another Location’ – this will copy the unique values in the column(s) into another column (or set of columns) somewhere on the same sheet
• List range – this is the where you select the column(s) which contain the values you want to get a list of unique values from. In the Topic Example Workbook, if we wanted to get a list of unique Sales people we would select Column A as the List Range. If we wanted to get a unique list of Sales people and Department combinations then we would select columns A:B.
• Criteria Range: Leave this blank for this purpose (unless you want to also apply multiple/complex criteria at the same time)
• Copy To: This will be enabled if you selected ‘Copy to Another Location’ above. Here you choose which column(s) you want the unique values to be listed in. Select the same number of columns here as the list range and note that the column(s) you select have has to be on the same sheet. So if we wanted to take the list of unique sales people in column A and put them into Column F we would select A:A as the List range and F:F as the Copy To Range
• Unique Records Only: Ensure that this is ticked.

If you selected ‘Copy to Another Location’ then when you click OK you should see that the unique values have been copied to the column you specified. If you selected ‘Filter the list, in place’ the data range should be appear as filtered, with only unique values in the column(s) you specified in the list range.

In the Topic Example Workbook, filter for a unique list of Sales people (column A) and populate the results into Column G on the same tab, using the method outlined above

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.