Count unique values in a list (some delimited)

[No Good At This]

Hi,

I hope this makes sense, but I need some VBA to count all of the unique items in a list such as:

1
2
3
4,5,6
3
2
1,2,3

As in, some cells will only contain one number but others will contain a number of them in a single cell.

Any help would be greatly appreciated

Thanks

 

[JoeMo]

Here's a UDF you can use on the worksheet as shown below the function. Assumes that your delimiter is a comma and your data are numbers:

Function CountUniques(R As Range) As Long
Dim d As Object, c As Range, vA As Variant
Set d = CreateObject("Scripting.dictionary")
d.RemoveAll
For Each c In R
    If InStr(c.Value, ",") > 0 Then
        vA = Split(c.Value, ",")
        For i = LBound(vA) To UBound(vA)
            If Not d.exists(Val(vA(i))) Then
                ct = ct + 1
                d.Add Val(vA(i)), ct
            End If
        Next i
    Else
        If Not d.exists(c.Value) Then
            ct = ct + 1
            d.Add c.Value, ct
        End If
    End If
Next c
CountUniques = d.Count
End Function

Excel Workbook
	A	B	C
1	1		6
2	2
3	3
4	4,5,6
5	3
6	2
7	1,2,3
Sheet4

 

[No Good At This]

That's perfect!

Thank you.

 

[JoeMo]

That's perfect!

Thank you.

You are welcome - thanks for the reply.