Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Best way to get a list of unique entries in a field
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Best way to get a list of unique entries in a field
You need the Advanced Filter, have a look here,
http://www.contextures.com/xladvfilter01.html Regards, Alan. wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Best way to get a list of unique entries in a field
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 wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting unique entries and assigning it to a named range | Excel Discussion (Misc queries) | |||
Count unique entries | Excel Worksheet Functions | |||
Count of unique entries | Excel Discussion (Misc queries) | |||
MRU list - entries keep changing | Excel Discussion (Misc queries) | |||
counting unique instances of text in a list | Excel Worksheet Functions |