|
Count Unique Values in a entire column, not just range
Howdy All,
I have a formula: =Sum(IF(Frequency(Match(A1:A14000,A1:A14000,0),Mat ch(A1:A14000,A1:A14000,0))0,1)) Which will give me a count if the unique values in the in column A, Rows 1 through 14000. What I need is a formula I can use to count the unique values in and entire Column, A:A. The above formula does not seem to allow that. Any ideas are greatly appreciated. Thanks, Brian |
Count Unique Values in a entire column, not just range
How about just leaving out 1 row?
=SUMPRODUCT((A2:A65536<"")/COUNTIF(A2:A65536,A2:A65536&"")) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Brian" wrote in message ... Howdy All, I have a formula: =Sum(IF(Frequency(Match(A1:A14000,A1:A14000,0),Mat ch(A1:A14000,A1:A14000,0))0,1)) Which will give me a count if the unique values in the in column A, Rows 1 through 14000. What I need is a formula I can use to count the unique values in and entire Column, A:A. The above formula does not seem to allow that. Any ideas are greatly appreciated. Thanks, Brian |
Count Unique Values in a entire column, not just range
What version of Excel are you using?
For an entire column, (65,536 or 1,048,576 rows) using Excel's built-in functions are pretty much out of the question. My machine locks up when I try this on more than 50,000 rows of data. Download and install the free Morefunc.xll add-in from: http://xcell05.free.fr/morefunc/english/index.htm Alternative download site: http://www.download.com/Morefunc/300...-10423159.html Then use the COUNTDIFF function. However, this function won't accept an entire column as a range reference. So, you'd have to set the range to one less cell: A1:A65535 A2:A65536 Also, see the help on COUNTDIFF since it has a few options as to exclusions. This function calculates very fast compared to a formula using buit-in functions. -- Biff Microsoft Excel MVP "Brian" wrote in message ... Howdy All, I have a formula: =Sum(IF(Frequency(Match(A1:A14000,A1:A14000,0),Mat ch(A1:A14000,A1:A14000,0))0,1)) Which will give me a count if the unique values in the in column A, Rows 1 through 14000. What I need is a formula I can use to count the unique values in and entire Column, A:A. The above formula does not seem to allow that. Any ideas are greatly appreciated. Thanks, Brian |
Count Unique Values in a entire column, not just range
Hello,
I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
If the OP wants the most efficient method they *should* use the Morefunc
add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
Thanks to All.
I've load the CountU and I am using that one. The reason I choose it was because I have to distribute the workbook and not everyone will have the MoreFunc add-in. Thanks again, Brian "T. Valko" wrote in message ... If the OP wants the most efficient method they *should* use the Morefunc add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
The reason I choose it was because I have to distribute the workbook and not
everyone will have the MoreFunc add-in. Actually, the add-in has an option where it allows one to embed it within the workbook. So other's don't need to install on their computer. Note, however, it's not compatible with Mac computers. -- Domenic http://www.xl-central.com In article , "Brian" wrote: Thanks to All. I've load the CountU and I am using that one. The reason I choose it was because I have to distribute the workbook and not everyone will have the MoreFunc add-in. Thanks again, Brian "T. Valko" wrote in message ... If the OP wants the most efficient method they *should* use the Morefunc add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
In addition, it doesn't look like COUNTU accepts a conditional
statement, such as... =COUNTU(IF(A2:A100="X",B2:B100)) It returns #VALUE! Or is this because the VBA code is not compatible with my Mac computer? -- Domenic http://www.xl-central.com In article , "T. Valko" wrote: If the OP wants the most efficient method they *should* use the Morefunc add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer? Nope, only takes a range argument. COUNTDIFF will take conditionals. Array entered: =COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALS E) Didn't time this but the "eyeball test" says it's still very fast. -- Biff Microsoft Excel MVP "Domenic" wrote in message ... In addition, it doesn't look like COUNTU accepts a conditional statement, such as... =COUNTU(IF(A2:A100="X",B2:B100)) It returns #VALUE! Or is this because the VBA code is not compatible with my Mac computer? -- Domenic http://www.xl-central.com In article , "T. Valko" wrote: If the OP wants the most efficient method they *should* use the Morefunc add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
Nope, only takes a range argument.
That's great, thanks Biff! -- Domenic http://www.xl-central.com In article , "T. Valko" wrote: It returns #VALUE! Or is this because the VBA code is not compatible with my Mac computer? Nope, only takes a range argument. COUNTDIFF will take conditionals. Array entered: =COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALS E) Didn't time this but the "eyeball test" says it's still very fast. -- Biff Microsoft Excel MVP "Domenic" wrote in message ... In addition, it doesn't look like COUNTU accepts a conditional statement, such as... =COUNTU(IF(A2:A100="X",B2:B100)) It returns #VALUE! Or is this because the VBA code is not compatible with my Mac computer? -- Domenic http://www.xl-central.com In article , "T. Valko" wrote: If the OP wants the most efficient method they *should* use the Morefunc add-in. Not only is it the most efficient method you also get a library of useful functions. I tested the COUNTU function against the COUNTDIFF function (using Charles Williams RangeTimer method). Filled A2:A65536 with =ROW(). 65,535 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 1.42 secs COUNTDIFF(A2:A65536) = 0.04 secs Filled A2:A65536 with random numbers from 0 to 100. 101 unique entries. Average calculation time (5 calculations): COUNTU(A2:A65536) = 0.38 secs COUNTDIFF(A2:A65536) = 0.07 secs -- Biff Microsoft Excel MVP "Bernd P" wrote in message ... Hello, I suggest NOT to use the SUMPRODUCT divided by COUNTIF approach (please see entry 3 of my Excel Dont's: http://www.sulprobil.com/html/excel_don_ts.html ) and NOT to use the Morefunc addin (entry 1 of that same list) but to take Charles Williams' COUNTU function. Please find my analysis on this he http://www.sulprobil.com/html/count_unique.html Regards, Bernd |
Count Unique Values in a entire column, not just range
Hello Biff,
I see but its the license argument. Regards, Bernd |
| All times are GMT +1. The time now is 01:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com