Best way to get a list of unique entries in a field

[samer.kurdi]

I often need to create a list of the unique entries from a field.
Typically I would do this using the countif function as follows:

- Say the field is column c, I would enter =countif(c$1:c2,c2) in a
column and copy all the way down, then filter for all the (1's)

However, my dataset has grown to around 100,000 rows across 2 sheets,
and doing this is getting to be too annoying and resource intensive.

A simple autofilter suggests that Excel already knows all the unique
entries in a column, as evident when clikcing down the header row in an
autofiltered block of data. Is there any way to just get that
information without having to create formulas across thousands of rows?

Or, simply, what is the best and most efficient way to get a list of
unique entries in a column?

Thanks

 

[Alan]

You need the Advanced Filter, have a look here,
http://www.contextures.com/xladvfilter01.html
Regards,
Alan.

 

[Biff]

Hi!

Use the advanced filter and filter on unique values only. You can either
filter in place or copy the uniques to another location.

I just tried it on a range of 50,000 rows (copied the uniques to another
location) so it will work on a range of at least that size.

Biff