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.
3 Answers
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.
=IF(MAX(COUNTIF(A1:A65536,A1:A65536))>1,"",A1)
Assuming your data is in column A: Copy the formula to B1 and use autofill down
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.