Pretty often I want to pick unique values from a table and list them in another, mostly to make statistics or summaries. A child can do this manually, shouldn't be too hard a task for my computer to do the same thing? Of course I may use VB, control buttons ect, but this is not very user friendly when shared with other users, and security settings confuse even more.


Data Tab - Sort & Filter Group - Advanced - copy to another location - unique records only - OK


This formula checks for duplicate entries using COUNTIF and MAX and a range which moves subsequently downwards. The last entry will be kept.


Assuming your data is in column A: Copy the formula to B1 and use autofill down

enter image description here


See also, this question here - including my alternative approach using pivot tables:

How do I get the distinct values in a column in Excel?

If you've customised your shortcuts/ribbon/QAT in Excel to add an 'Insert PivotTable' shortcut, this can be a very speedy way to get unique records.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.