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.
 

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

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

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 he
http://www.thecommon.net/9.html


RBS



"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 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

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

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

Calculating the number of unique values within a large range.
 

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


"RB Smissaert" wrote in message
...
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 he
http://www.thecommon.net/9.html


RBS



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