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:

Location,performance

errors,ip address,impact

ip address, identification, transaction

Performance, identification

historic, performance, UI

performance, consolidation, identification

determination, performance

solutions, overview

UI, reporting

historic data

performance, reporting, consolidation

reporting, KPI

analysis

application mapping, dependencies

monitoring

<colgroup><col></colgroup><tbody>
</tbody>

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:

Akamai

analysis

application mapping

availability

baseline

change management

citrix

comparison

compliance

consolidation

customization

dependencies

determination

drilldown

errors

historic data

identification

impact

ip address

KPI

latency

Location

logging

monitoring

organization

overview

performance

problem determination

process

QA

reporting

RUM

solutions

SQL

testing

transaction

transactions

<colgroup><col></colgroup><tbody>
</tbody>

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)

.ListObjects("Table2").ListColumns("Keywords")

apples, Oranges

Bananas, Apples, oranges

Apples

Oranges

Pears, apples, Oranges, Grapes

grapes

<tbody>
</tbody>

Target Range (Unique Keywords)

.ListObjects("Table5").ListColumns("Keywords")

Apples

Oranges

Bananas

Pears

Grapes

<tbody>
</tbody>

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)

.ListObjects("Table2").ListColumns("Keywords")

apples, Oranges

Bananas, Apples, oranges

Apples

Oranges

Pears, apples, Oranges, Grapes

grapes

<tbody>
</tbody>

Target Range (Unique Keywords)

.ListObjects("Table5").ListColumns("Keywords")

Apples

Oranges

Bananas

Pears

Grapes

<tbody>
</tbody>

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