De: Do Bee <todfour@flex.net>
Objet: Re: Count Unique
Date : vendredi 28 juillet 2000 01:18

This will return the number of unique characters in a string.

Sub UniqueCharacters()
Dim UniqueCount As Integer
Dim EvalString As String

EvalString = Range("A1")

UniqueCount = 0
Do Until Len(EvalString) = 0
    i = 1
    If Left(EvalString, 1) = Mid(EvalString, i, 1) Then
        UniqueCount = UniqueCount + 1
        EvalString = Replace(EvalString, Mid(EvalString, i, 1), "")
        i = i + 1
    End If
Loop

MsgBox "There are " & UniqueCount & " unique characters in this string."

End Sub

If you want it to go down a list of values,  use it as a function, like
this:

Function UniqueCharacters(EvalString As String) As Integer

UniqueCount = 0
Do Until Len(EvalString) = 0
    i = 1
    If Left(EvalString, 1) = Mid(EvalString, i, 1) Then
        UniqueCount = UniqueCount + 1
        EvalString = Replace(EvalString, Mid(EvalString, i, 1), "")
        i = i + 1
    End If
Loop
    UniqueCharacters = UniqueCount
End Function

Sub TestIt()
set WorkRange = Range("A1:A20")
For Each Cell In WorkRange
    Cell.Offset(0, 1).Value = UniqueCharacters(Cell.Value)
Next Cell

End Sub


"Mark" <mobrien@biacompanies.com> wrote in message
news:3980a616$1@newsprime.tidalwave.net...
> I need to be able to count the unique instances of a series of numbers in
a
> column.  For example, 111222333 would return 3, since there are three
unique
> values in that list.  I assume I must have the values sorted to put
similar
> numbers together, which is not preferred but doable. Ideally, I would like
it
> to work with both numbers and text.  Is there a way to do this?
>
> The only solution I could come up with was to add a column, put a 1 in
that
> new column when each number starts a new unique series, then count in the
new
> column convert that formula to a value and delete the new column.  There
must
> be an easier way.
>
> Thanks,
> Mark

 