Find unique entries

We can’t all be unique – then we’d be the same.

By Neale Blackwood
Ever need to find the unique entries in a list? You can see them listed in the Autofilter drop down, but how do you get that list onto the sheet?

You have to use the Advanced Filter. In Excel 2003 and earlier this is in the Data menu under Filter. In Excel 2007 it is in the Data Ribbon tab in the Sort & Filter section.

I usually copy the list (including its field name) to another sheet before using the Advanced Filter. This means I don’t affect the existing list.

To extract a unique list of entries click on the list then open the Advanced Filter. The List Range should already be selected. Click the Unique records only checkbox, click the Copy to another location option, click inside the Copy to: box and select a cell. Click OK. See example below.

The unique entries should be created from the cell you selected down the sheet.

Alternatively, by selecting the Filter the list, in-place checkbox, duplicates are temporarily hidden.