Leading the way in Microsoft Office Development

Filtering out Unique Records

You can also use the Advanced Filter to filter a table of data and only display unique records.

This feature is extremely useful but you need to be aware of the difference between unique values and unique records.

Using the "Unique records only" checkbox will only remove rows that are identical in all the columns.

Either all the columns in the original table or all the columns that are copied to another location.

In this example we are just going to filter out everyone that has an "Age" of 22.

Filtering - All Records (Two Columns)

When you are copying the results to another location it is possible to copy a selection columns.

Lets consider the above table and lets suppose we wanted to filter the table and copy the "First Name" and "Age" columns to a new location.

Select a cell inside the table, for example cell "C7" and select (Data > Filter > Advanced Filter).

Check that the "List range" references the whole table.

In the "Criteria range" textbox select the cells "B2:B3".

Select the option "Copy to another location".

In the "Copy to" textbox instead of just selecting the first cell where you want the results to start you can actually specify which columns you want displayed.

In this example we want the "First Name" and "Age" columns displayed so our "Copy To" range looks as follows:

In the "Copy to" textbox select the cells "B19:C19".

Filtering - Unique Records (Two Columns)

Now lets do exactly the same filter but this time select "Unique records only".

An additional filter is now added which removed any duplicate rows from the rows that have been successfully filtered.

Things to Remember

Criteria on the same line (different columns) are joined by AND and criteria on a new line (same column) are joined by "OR".

You can quickly display all the rows from a filtered list by selecting (Data > Filter > Show All).