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.
Excel Conditional Formatting Zebra Stripes
Jim
I used to do horrible things like this
or copy a list, sort it, set up formulae and filter to get the unique values
then a colleague (God bless you, Dom) showed me the Remove Duplicates button (on the Data ribbon), it’s SO useful that I’ve added it to my QAT (and it’s Undo-able, so you can unique-ify a range in situ for a quick look-see)
Mynda Treacy
Another nice tip, Jim. Maybe you should write a book of tips 🙂
Rajesh
Hello Friends
It’s the best place where will you get best solution of your excel problems.
I got and I’m satisfied with this.
Best Regards,
Mynda Treacy
Cheers, Rajesh 🙂