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.
The only real limitation is that you can only copy filtered data to the active sheet. No big deal. Just copy the complete list to the sheet you want before you use the Advanced Filter.
If you liked this please click the Facebook button below and share it with your friends 🙂
Jim says
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 says
Another nice tip, Jim. Maybe you should write a book of tips 🙂
Rajesh says
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 says
Cheers, Rajesh 🙂