Do you ever want to extract a list of unique records from a massive list like the one in column A below which has over 900 records?
It would be tiresome to copy and paste the list and then delete the duplicate lines.
Of course we could use the Remove Duplicates tool on the Data tab of the ribbon, but we’re going to try something different today.
And that is to use Excel’s Advanced Filter to extract a list of unique records.
Excel Advanced Filter
It’s dead easy. Simply select the cells containing the list you want to filter >
- On the Data tab of the ribbon select Advanced Filter
- Select ‘Copy to another location’, check your ‘List range’ is correct, enter the cell you want your list copied to, then check the ‘Unique records only’ check box.
Voila. You now have a list of unique records.
Note: If you want to copy unique records to another sheet, be sure to select a cell on that sheet before going to the Data tab > Advanced Filter.
Alternatives to Excel Advanced Filter
If you have Office 365 you can use the new UNIQUE Function to extract a distinct or unique list.