Calculating the number of unique values within a large range
 

I would be most grateful if someone could offer me some help.

I need to count the number of unique values within a column range. The range
is between B2:B65536. Each value consists of a series of alpha and numerical
characters and there are blank cells within the range.

There will be around 30,000 plus entries.

I have used various formulas, but due to the vast number of rows of data
they crash.

An example of one of the formulas I used is below:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

I am desperate for an answer but am confused as to how to do this? Any help
or assistance would be very much greatly appreciated.

Calculating the number of unique values within a large range
 

Why are you posting in two forums?


--
Pecoflyer
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24219

Calculating the number of unique values within a large range
 

Download and install the free add-in Morefunc.xll from this site:

http://xcell05.free.fr/morefunc/english/index.htm

It has a function called COUNTDIFF that will do what you want.

--
Biff
Microsoft Excel MVP


"SiH23" wrote in message
...
I would be most grateful if someone could offer me some help.

I need to count the number of unique values within a column range. The
range
is between B2:B65536. Each value consists of a series of alpha and
numerical
characters and there are blank cells within the range.

There will be around 30,000 plus entries.

I have used various formulas, but due to the vast number of rows of data
they crash.

An example of one of the formulas I used is below:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

I am desperate for an answer but am confused as to how to do this? Any
help
or assistance would be very much greatly appreciated.

Calculating the number of unique values within a large range
 

Hi,

I don't think the formula you entered will work.

If there are no blank cells in the range you can use

=SUM(1/COUNTIF(B2:B65536,B2:B65536))

entered as an array; or without an array:

=SUMPRODUCT(1/COUNTIF(B2:B65536,B2:B65536))

These function are very slow when going against 65,000 row.

Here is one formula which takes blanks into account:

=SUM(1/(IF(COUNTIF(B2:B65536,B2:B65536)0,COUNTIF(B2:B655 36,B2:B65536),1)))-(COUNTBLANK(B2:B65536)0)

This is array entered. if you know there are blanks you can just replace
(COUNTBLANK(B2:B65536)0) with 1.

--
Thanks,
Shane Devenshire


"SiH23" wrote:

I would be most grateful if someone could offer me some help.

I need to count the number of unique values within a column range. The range
is between B2:B65536. Each value consists of a series of alpha and numerical
characters and there are blank cells within the range.

There will be around 30,000 plus entries.

I have used various formulas, but due to the vast number of rows of data
they crash.

An example of one of the formulas I used is below:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

I am desperate for an answer but am confused as to how to do this? Any help
or assistance would be very much greatly appreciated.

Calculating the number of unique values within a large range
 

These function are very slow when going against 65,000 row.

They cause my machine to crash. But then, my machine is fairly "gutless" by
todays standards.

The function in the add-in calculates 65535 rows in the blink of an eye.


--
Biff
Microsoft Excel MVP


"ShaneDevenshire" wrote in
message ...
Hi,

I don't think the formula you entered will work.

If there are no blank cells in the range you can use

=SUM(1/COUNTIF(B2:B65536,B2:B65536))

entered as an array; or without an array:

=SUMPRODUCT(1/COUNTIF(B2:B65536,B2:B65536))

These function are very slow when going against 65,000 row.

Here is one formula which takes blanks into account:

=SUM(1/(IF(COUNTIF(B2:B65536,B2:B65536)0,COUNTIF(B2:B655 36,B2:B65536),1)))-(COUNTBLANK(B2:B65536)0)

This is array entered. if you know there are blanks you can just replace
(COUNTBLANK(B2:B65536)0) with 1.

--
Thanks,
Shane Devenshire


"SiH23" wrote:

I would be most grateful if someone could offer me some help.

I need to count the number of unique values within a column range. The
range
is between B2:B65536. Each value consists of a series of alpha and
numerical
characters and there are blank cells within the range.

There will be around 30,000 plus entries.

I have used various formulas, but due to the vast number of rows of data
they crash.

An example of one of the formulas I used is below:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

I am desperate for an answer but am confused as to how to do this? Any
help
or assistance would be very much greatly appreciated.