Calculating the number of unique values within a large range

[SiH23]

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.

 

[T. Valko]

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.

 

[ShaneDevenshire]

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:B65536,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.

 

[T. Valko]

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.