Count Unique Values in a entire column, not just range

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

Howdy All,

I have a formula:

=Sum(IF(Frequency(Match(A1:A14000,A1:A14000,0),Match(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
 
How about just leaving out 1 row?

=SUMPRODUCT((A2:A65536<>"")/COUNTIF(A2:A65536,A2:A65536&""))
 
How about just leaving out 1 row?

=SUMPRODUCT((A2:A65536<>"")/COUNTIF(A2:A65536,A2:A65536&""))
 
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/3000-2077_4-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.
 
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/3000-2077_4-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.
 
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
 
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
 
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
 
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
 
The reason I choose it was because I have to distribute the workbook and not
everyone will have the MoreFunc add-in.
Click to expand...

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.
 
The reason I choose it was because I have to distribute the workbook and not
everyone will have the MoreFunc add-in.
Click to expand...

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.
 
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?
 
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?
 
It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer?
Click to expand...

Nope, only takes a range argument.

COUNTDIFF will take conditionals.

Array entered:

=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALSE)

Didn't time this but the "eyeball test" says it's still very fast.
 
It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer?
Click to expand...

Nope, only takes a range argument.

COUNTDIFF will take conditionals.

Array entered:

=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALSE)

Didn't time this but the "eyeball test" says it's still very fast.
 
Nope, only takes a range argument.

That's great, thanks Biff!

--
Domenic
http://www.xl-central.com


It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer?
Click to expand...

Nope, only takes a range argument.

COUNTDIFF will take conditionals.

Array entered:

=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALSE)

Didn't time this but the "eyeball test" says it's still very fast.


--
Biff
Microsoft Excel MVP


Domenic said:
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?
Click to expand...
[/QUOTE]
 
Nope, only takes a range argument.

That's great, thanks Biff!

--
Domenic
http://www.xl-central.com


It returns #VALUE! Or is this because the VBA code
is not compatible with my Mac computer?
Click to expand...

Nope, only takes a range argument.

COUNTDIFF will take conditionals.

Array entered:

=COUNTDIFF(IF(B2:B65536=2008,A2:A65536),FALSE,FALSE)

Didn't time this but the "eyeball test" says it's still very fast.


--
Biff
Microsoft Excel MVP


Domenic said:
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?
Click to expand...
[/QUOTE]
 
Back
Top