Convert Column of comma delimited Keyword/phrases to a range of unique values

[webguynj]

I have a column in a Table with keywords.

Here's an example:
[TABLE="class: grid, width: 200"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Location,performance[/TD]
[/TR]
[TR]
[TD]
errors,ip address,impact

[/TD]
[/TR]
[TR]
[TD]ip address, identification, transaction[/TD]
[/TR]
[TR]
[TD]
Performance, identification[/TD]
[/TR]
[TR]
[TD]historic, performance, UI[/TD]
[/TR]
[TR]
[TD]performance, consolidation, identification[/TD]
[/TR]
[TR]
[TD]determination, performance[/TD]
[/TR]
[TR]
[TD]solutions, overview[/TD]
[/TR]
[TR]
[TD]UI, reporting[/TD]
[/TR]
[TR]
[TD]historic data[/TD]
[/TR]
[TR]
[TD]performance, reporting, consolidation[/TD]
[/TR]
[TR]
[TD]reporting, KPI[/TD]
[/TR]
[TR]
[TD]analysis

---

[/TD]
[/TR]
[TR]
[TD]application mapping, dependencies[/TD]
[/TR]
[TR]
[TD]monitoring[/TD]
[/TR]
</tbody>[/TABLE]

I want to take this and turn it into a range for a filter I have set up and the output needs to looks like:

[TABLE="class: grid, width: 100"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Akamai[/TD]
[/TR]
[TR]
[TD]analysis[/TD]
[/TR]
[TR]
[TD]application mapping [/TD]
[/TR]
[TR]
[TD]availability [/TD]
[/TR]
[TR]
[TD]baseline [/TD]
[/TR]
[TR]
[TD]change management [/TD]
[/TR]
[TR]
[TD]citrix[/TD]
[/TR]
[TR]
[TD]comparison[/TD]
[/TR]
[TR]
[TD]compliance[/TD]
[/TR]
[TR]
[TD]consolidation [/TD]
[/TR]
[TR]
[TD]customization [/TD]
[/TR]
[TR]
[TD]dependencies [/TD]
[/TR]
[TR]
[TD]determination [/TD]
[/TR]
[TR]
[TD]drilldown [/TD]
[/TR]
[TR]
[TD]errors [/TD]
[/TR]
[TR]
[TD]historic data[/TD]
[/TR]
[TR]
[TD]identification[/TD]
[/TR]
[TR]
[TD]impact[/TD]
[/TR]
[TR]
[TD]ip address [/TD]
[/TR]
[TR]
[TD]KPI[/TD]
[/TR]
[TR]
[TD]latency [/TD]
[/TR]
[TR]
[TD]Location [/TD]
[/TR]
[TR]
[TD]logging [/TD]
[/TR]
[TR]
[TD]monitoring [/TD]
[/TR]
[TR]
[TD]organization [/TD]
[/TR]
[TR]
[TD]overview[/TD]
[/TR]
[TR]
[TD]performance[/TD]
[/TR]
[TR]
[TD]problem determination[/TD]
[/TR]
[TR]
[TD]process[/TD]
[/TR]
[TR]
[TD]QA [/TD]
[/TR]
[TR]
[TD]reporting[/TD]
[/TR]
[TR]
[TD]RUM [/TD]
[/TR]
[TR]
[TD]solutions [/TD]
[/TR]
[TR]
[TD]SQL [/TD]
[/TR]
[TR]
[TD]testing[/TD]
[/TR]
[TR]
[TD]transaction[/TD]
[/TR]
[TR]
[TD]transactions [/TD]
[/TR]
</tbody>[/TABLE]

I'm sure this can't be a unique request and I looked at this thread that I thought was similar but it doesn't help me out. Is there an easy way to do this?

 

[mirabeau]

I have a column in a Table with keywords.

Here's an example:
...

I want to take this and turn it into a range for a filter I have set up and the output needs to looks like:
...

I'm sure this can't be a unique request and I looked at this thread that I thought was similar but it doesn't help me out. Is there an easy way to do this?

I don't see much relationship between your input table and your output table.

Like where do "Akamai", "QA" and etc come from? Should anyone answering guess at these?

However, who knows?, maybe something like this VBA code might be on the lines of what you want

Sub reorganize()
Dim d As Object, rws As Long
Dim c, e
Set d = CreateObject("scripting.dictionary")
rws = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("A1").Resize(rws)
    For Each e In Split(c, ",")
        d(Trim(e)) = 1
    Next
Next
With Range("C1").Resize(d.Count)
    .Value = Application.Transpose(d.keys)
    .Sort .Cells(1), 1, Header:=xlNo
End With
End Sub

 

[webguynj]

I apologize if the samples above were incomplete, but I need to work from a source in .ListObjects("Table2").ListColumns("Keywords") where there are many phrases comma delimited to a unique list of phrases in .ListObjects("Table5").ListColumns("Keywords"). The target is then used as the source for a DropDown List to use to AutoFilter the original table/column.

As new keywords or phrases are added I want to be able to use the onChange event in the source Keywords column to regenerate the target range.

I could not get the sample above to work. and I am pretty good with code - just not an expert in the excel object model.

 

[webguynj]

One last thing -
Set d = CreateObject("scripting.dictionary")

Does not work on Excel 2011 as near as I can tell.

 

[webguynj]

Here is a better set of sample data, but not real data as an example:

Source Range (multiple keywords)

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD].ListObjects("Table2").ListColumns("Keywords")[/TD]
[/TR]
[TR]
[TD]apples, Oranges[/TD]
[/TR]
[TR]
[TD]Bananas, Apples, oranges[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Pears, apples, Oranges, Grapes[/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[/TR]
</tbody>[/TABLE]


Target Range (Unique Keywords)
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD].ListObjects("Table5").ListColumns("Keywords")[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[/TR]
</tbody>[/TABLE]

The target range is used as the source for a drop down list. I want to be able to run the macro the updates the target range anytime the source range is modified.

 

[mirabeau]

One last thing -
Set d = CreateObject("scripting.dictionary")

Does not work on Excel 2011 as near as I can tell.

I guess then that you're using a Mac.

Microsoft Office for Macs is different from MS Office for Windows. Includes not supporting the scripting dictionary object that can be so very convenient when using Windows versions.

There's ways around that OK, some of which I've done a number of times in the past.

But I just don't have time now to look at it again for some hours at least. If nobody else responds in the meantime, perhaps you could bump back the thread and see if anything more useful happens then.

 

[mirabeau]

Here is a better set of sample data, but not real data as an example:

Source Range (multiple keywords)

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD].ListObjects("Table2").ListColumns("Keywords")[/TD]
[/TR]
[TR]
[TD]apples, Oranges[/TD]
[/TR]
[TR]
[TD]Bananas, Apples, oranges[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Pears, apples, Oranges, Grapes[/TD]
[/TR]
[TR]
[TD]grapes[/TD]
[/TR]
</tbody>[/TABLE]


Target Range (Unique Keywords)
[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD].ListObjects("Table5").ListColumns("Keywords")[/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[/TR]
[TR]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]Grapes[/TD]
[/TR]
</tbody>[/TABLE]

The target range is used as the source for a drop down list. I want to be able to run the macro the updates the target range anytime the source range is modified.

Try this code, which doesn't use dictionary objects or similar.
Your original list in ColumnA with header, output is in ColumnF.

Sub make_list()
Dim u(), v(), b() As Boolean
Dim rws As Long, i As Long, j As Long
Dim c As Long, q As Long
Dim a As Variant, e As Variant, x As Variant

rws = Range("A" & Rows.Count).End(xlUp).Row
a = Range("A2").Resize(rws - 1)
For i = 1 To rws - 1
    x = Split(a(i, 1), ",")
    ReDim Preserve u(1 To q + UBound(x) + 1)
    For Each e In x
        q = q + 1
        u(q) = StrConv(Trim(e), vbProperCase)
    Next e
Next i
ReDim v(1 To q, 1 To 1), b(1 To q)

For i = 1 To q
If Not b(i) Then
    c = c + 1
    v(c, 1) = u(i)
    For j = i To q
        If u(j) = u(i) Then b(j) = True
    Next j
End If
Next i

Range("F2").Resize(c) = v

End Sub