2

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 3

2

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

0

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

enter image description here

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .