How do I sort out duplicates using a formula in excel? Listing the unique values in adjacent cells. Without using VBA?

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.