Friday, December 10, 2004

Using the Advanced Filter Function in Excel to lcoate unique records

Identifying the unique records or items in a long list can be arduous. However there is actually a very easy way.
I use the unique records option within the Advanced Filter function as follows.

1. Select the list of items you want to find the unique records in. This should be a single column of data and it needs to have a header. The selection can include spaces (blank cells).
2. Copy the selection and paste into a blank workbook (CTRL + N to open a blank workbook)
3. Ensure that the selection includes the header (this can be anything)
4. Select from the menu Data - Filter - Advanced Filter. (Click OK if a message appears.)
5. Ensure "Filter the list, in-place" is selected and then select "Unique records only" and click OK.

The list should now display the unique records only. Note the row numbers have gone blue. This indicates that the duplicate records are just hidden. You can copy and paste the visible selection back to your original worksheet as values (Paste Special)


Posted by: Jethro | 11:56 PM |