Counting unique values

**Jogier505** · 02-14-2011, 04:44 PM

I am looking for a formula that will look at the values in column "C" and return a sequential count of column "C" in column "D". The attached file should make my desired result much clearer.

JIm O

**NBVC** · 02-14-2011, 04:47 PM

Try:

=IF(C2=C1,D1,D1+1)

copied down

or

=D1+(C2<>C1)

**Jogier505** · 02-14-2011, 04:55 PM

NBVC,

You are a god send. I have over 2000 rows to count and I was not looking forward to a manual count of any kind.

Thank you.
Jim O

**mrice** · 02-14-2011, 04:53 PM

You could try this user defined function.

Function CountUniqueValues(MyRange As Range) As Long
Dim MyArray() As String
Dim Cell As Range
Dim NewValue As Boolean
Dim N As Long
ReDim MyArray(0)
For Each Cell In MyRange
    NewValue = True
    For N = 1 To UBound(MyArray)
        If MyArray(N) = Cell.Value Then
            NewValue = False
            Exit For
        End If
    Next N
    If NewValue = True Then
        ReDim Preserve MyArray(UBound(MyArray) + 1)
        MyArray(UBound(MyArray)) = Cell.Value
    End If
Next Cell
CountUniqueValues = UBound(MyArray)
End Function

Use =CountUniqueValues(C$2:C2) in Cell D2