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.

 

[David]

Hi,
From a copy of the worksheet, sort it on the column you are trying to find
the unique values in, Column B. Then use Data/Filter/Advanced Filter/Unique
Records Only - This will give you the unique records.

David

 

[RB Smissaert]

This VBA code will run quite fast:

Function CountUnique(rng As Range) As Long

Dim i As Long
Dim arr
Dim coll As Collection

arr = rng

Set coll = New Collection

On Error Resume Next
For i = 1 To UBound(arr)
coll.Add arr(i, 1), CStr(arr(i, 1))
Next i

CountUnique = coll.Count

End Function


Sub test()

MsgBox CountUnique(Selection)

End Sub

So, in your case select the range B2:B65536 and run the Sub test.
If still not fast enough than use Olaf Schmidt's dhSortedDictionary, which
can be downloaded from here:
http://www.thecommon.net/9.html


RBS

 

[Mike H]

Hi,

I suspect you've created a monster with a column of 30k data entries. Try
this if you want to count unique ones.

=SUMPRODUCT((B2:B1000<>"")/(COUNTIF(B2:B1000,B2:B1000&"")))

It will take a long time to calculate if you use 65535 cells as in your
example so I suggest you switch calculation to manual to prevent frequent
recalculation.

Mike

 

[Bernd P]

Hello,

I suggest to avoid formulas with sumproduct/countif for this task.

See http://www.sulprobil.com/html/excel_don_ts.html

Look for Charles Williams' function COUNTU in his article.

Regards,
Bernd

 

[RB Smissaert]

Just a correction to do with the posted link.
dhSortedDictionary should not be used (it can cause an error) and instead
one should use the file
dhRichClient, which can be downloaded from:
www.datenhaus.de/Downloads/dhRichClientDemo.zip
This has an updated version of cSortedDictionary and beside that a lot more,
eg a very good
VB(A) wrapper for SQLite.

RBS