Calculating the number of unique values within a large range

S

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.
 
S

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top