Unique values from a comma separated column in Excel

Question

Given a sheet that looks like this, one column full of comma separated values:

Col 1

"a,b,c"
"c,b,a"
"a,b"
"b,a"
"b,a"
"x,y,z"

I would like to be able to list all unique values in that column of data. I should be presented with a,b,c,x,y,z

Is there a way to do this within Excel 2010?

Answer 1

Consider:

Sub Uniqquuee()
    Dim N As Long, i As Long, C As Collection
    Dim dq As String

    dq = Chr(34)
    Set C = New Collection
    N = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        ary = Split(Replace(Cells(i, 1).Text, dq, ""), ",")
        For Each a In ary
            On Error Resume Next
                C.Add a, CStr(a)
            On Error GoTo 0
        Next a
    Next i

    For i = 1 To C.Count
        st = st & "," & C.Item(i)
    Next i
    MsgBox Mid(st, 2)
End Sub