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.

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

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



"SiH23" <(E-Mail Removed)> wrote in message
news:2B1CB709-4EB4-4E96-991F-(E-Mail Removed)...
>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.
>

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

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

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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
>
>
>
> "SiH23" <(E-Mail Removed)> wrote in message
> news:2B1CB709-4EB4-4E96-991F-(E-Mail Removed)...
>>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.
>>
>