A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Count Unique Values in a entire column, not just range



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2009, 10:47 PM posted to microsoft.public.excel.worksheet.functions
Brian[_16_]
external usenet poster
 
Posts: 34
Default 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


  #2  
Old May 15th, 2009, 10:55 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,482
Default 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



  #3  
Old May 15th, 2009, 11:07 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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



  #4  
Old May 16th, 2009, 12:09 AM posted to microsoft.public.excel.worksheet.functions
Bernd P
external usenet poster
 
Posts: 613
Default 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


  #5  
Old May 16th, 2009, 03:41 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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




  #6  
Old May 16th, 2009, 04:46 PM posted to microsoft.public.excel.worksheet.functions
Brian[_16_]
external usenet poster
 
Posts: 34
Default 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






  #7  
Old May 16th, 2009, 05:25 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default 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




  #8  
Old May 16th, 2009, 05:49 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default 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


  #9  
Old May 16th, 2009, 06:40 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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




  #10  
Old May 16th, 2009, 07:41 PM posted to microsoft.public.excel.worksheet.functions
Domenic[_2_]
external usenet poster
 
Posts: 265
Default 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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.