Cell with "multiple" values separated in slicers as unique values? (EXCEL 2013)

[rabert]

Let's say I have three columns where column C can contain "multiple" values like "John, Pete" instead of just "John" or "Pete". See table below...

Is it possible in some way to make the slicer for Column C just show the unique values "John", "Pete", "George", instead of the combined ones like "George, John"?
I would not like to use several rows to define unique values in the slicer for column C. Otherwise I don't care how it can be fixed. New columns with formulas, advanced slicer options or VBA etc? Doesn't matter as long as only one slicer displays unique values....


[TABLE="width: 251"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD]2[/TD]
[TD]John, Pete[/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD]3[/TD]
[TD]Pete[/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD]4[/TD]
[TD]George, John[/TD]
[/TR]
</tbody>[/TABLE]

 

[venkat1926]

data is like this

Excel Workbook
	A	B	C
1	hdng1	hdng2	hdng3
2	Red	2	John, Pete
3	Green	3	Pete
4	Blue	4	George, John
Sheet1

run the macro "test" and see column D

Sub test()
    Dim j As Integer, r As Range, c As Range, k As Integer, m As Integer, n As Integer
    Dim x() As String, y As String, p As Integer
    Range("d1").EntireColumn.Delete
    Set r = Range(Range("C2"), Range("C2").End(xlDown))
    For Each c In r
        j = Len(c)
        k = Len(WorksheetFunction.Substitute(c, ",", ""))
        'msgbox j: 'msgbox k
        p = j - k
        'msgbox p
        'this is number of commas in c
        m = 1
        ReDim x(1 To p + 1)
        y = ""
        For k = 1 To p + 1
            On Error GoTo nextstep
            n = WorksheetFunction.Search(",", c)


            'msgbox n
nextstep:
            x(k) = Mid(c.Value, m, n - 1)
            'msgbox x(k)
            y = y & """" & x(k) & """" & ","
            'msgbox y
            m = n + 1
        Next k
        'msgbox y
        c.Offset(0, 1) = Left(y, Len(y) - 2)
        y = ""
    Next c
    Range("d1").EntireColumn.AutoFit
    MsgBox "macro over"
End Sub

 

[rabert]

Thanks for the reply!

This is what I get:

This would still result in multiple values in a slicer. What outcome did you have in mind?

 

[venkat1926]

what do you want as outcome???? atleasts in this ample case ?

 

[rabert]

In column C there can be multiple names, they will never spread over several columns. I want to a be able to separate these names in a slicer or vba-listbox or something like that.

So instead of a slicer or listbox showing three values with multiple names;

...I want the slicer or listbox to show;

So, for instance, when John is selected only row three should be filtered away since that row don't contain the name John.

 

[venkat1926]

sorry still not clear. does this mean columns A and B are not relevant. you want only the unique values of column C in that case do ADVANCED FILTER to colmn s in the advanced filter window copytorange shoud be different cell, critiera range blank, unique should be true.