I have a spreadsheet that includes a long list of reference numbers
(alphanumeric) in column A. These are in a random order and contain
many duplicates. I therefore wish to create a function that will count
the unique numbers for me.

I can do this by using advanced filter or by sorting and adding another
column to count when the reference changes, but do not want to change
the order of the data nor to have something that involves refiltering
every time. Ideally I need a formul;a that will allow an 'idiot user'
to see the count as they dynamically add or delete data.

Any thoughts would be gratefully received


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Andy

You could download and install LaurentLongre's addin MOREFUNC.XLL
(it will give you similar functionality and a "few" more very usefull
functions. http://longre.free.fr/english/

If you want to write your own code following could be a start.
(note it doesnt distinguish between upper and lower case text items)
but it's reasonably fast. (Never as fast as compiled XLL's)


Function UniqueItemCount(rng As Range, _
Optional bExcludeBlank As Boolean = True) As Long

Dim rArea As Range
Dim rCell As Range
Dim cItem As Collection
Const x As Byte = 1

Set cItem = New Collection
On Error Resume Next
For Each rArea In rng.Areas
For Each rCell In rArea.Cells
cItem.Add x, CStr(rCell.Value2)
Next
Next

UniqueItemCount = cItem.Count

If bExcludeBlank Then
If Not IsError(cItem(vbNullString)) Then
UniqueItemCount = UniqueItemCount - 1
End If
End If

End Function



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Andy wrote:


I have a spreadsheet that includes a long list of reference numbers
(alphanumeric) in column A. These are in a random order and contain
many duplicates. I therefore wish to create a function that will
count
the unique numbers for me.

I can do this by using advanced filter or by sorting and adding
another
column to count when the reference changes, but do not want to change
the order of the data nor to have something that involves refiltering
every time. Ideally I need a formul;a that will allow an 'idiot user'
to see the count as they dynamically add or delete data.

Any thoughts would be gratefully received


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Problem solved. Thanks


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!