[Solved] Create a dynamic list of products associated to a list of unique search keywords in VBA

by


You could use Dictionary object:

Sub test()
    Dim keywordColumn As String, productColumn As String
    Dim products As String
    Dim i As Integer
    Dim myKey, p

    'after adding reference to Microsoft Scripting Runtime
    'Dim Keywords As New Dictionary

    Dim Keywords As Object
    Set Keywords = CreateObject("Scripting.Dictionary")

    keywordColumn = "B"
    productColumn = "A"

    With ActiveSheet
        maxRow = .Cells(.Rows.Count, productColumn).End(xlUp).Row
        ' loop through each cell in the keywords column, ignoring the column header
        For i = 2 To maxRow
            ' the keywords are comma delimited so they must be Split()
            k = Split(.Cells(i, keywordColumn).Value, ",")

            For Each myKey In k
                If Not Keywords.Exists(myKey) Then
                    Keywords.Add key:=myKey, Item:=New Collection
                End If

                With .Cells(i, productColumn)
                    On Error Resume Next
                    Keywords(myKey).Add Item:=.Value, key:=CStr(.Value)
                    On Error GoTo 0
                End With                                        
            Next myKey
        Next i

        '**********************************************
        'OUTPUT
        '**********************************************

        i = 2
        'iterates through each key
        For Each myKey In Keywords.Keys                
            products = ""
            'iterates through each product corresponding to myKey 
            For Each p In Keywords(myKey)
                products = products & p & ", "
            Next
            'write in cells
            .Cells(i, "D") = myKey
            If products <> "" Then .Cells(i, "E") = Left(products, Len(products) - 2)

            i = i + 1
        Next

    End With
End Sub

RESULT:

enter image description here

Note: I’d recommend you to add reference to Microsoft Scripting Runtime library (go to Tools->References and select Microsoft Scripting Runtime). In that case you could use:

Dim Keywords As New Dictionary

instead 

Dim Keywords As Object
Set Keywords = CreateObject("Scripting.Dictionary")

Referencing to library makes your code faster and adds intellisence feature for your Keywords object.

1

solved Create a dynamic list of products associated to a list of unique search keywords in VBA