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 |
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 |
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 |
All times are GMT +1. The time now is 11:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com