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

I have a list of products, each of which has its own keywords that search for that product on the site .

Source example

  Products I want to create a list of unique keywords  and  products. Keywords -------- -------- Envelope 1, Envelope Envelope 2, Envelope Label 1 Label, Mailing Label 2 Label, Mailing Label 3 Label, Mailing, Address  

Generation list which I want ...

  keyword product -------- -------- 1 envelope 1 2 envelope 2 envelope envelope 1, Envelope 2 Label Label 1, Label 2, Label 3 Mailing Label 1, Label 2, Label 3 Address Label 3  

Then I will loop through the keywords and get the products for that keyword I will do Will perform h, and then to validate that the Products.

I can create an archive that contains a unique list of keywords but I am struggling to create an affiliate list of products. I think I want to use the nested collection as described, but I have a hard time finding the information because I want to add to a dynamic list.

  'In each of the loop keyword columns, the cell in the column column is ignored, I go to the maxRow = 2' keyword delimited comma so that they should be split () K = split ( ActiveSheet.Cells (i, keywordColumn) .Value, ",") for J = 0 UBound (K) to turn off the Error Error 457 Checking the error in the error resume the next keywords.Add the item: = K ( J), Key: = K (J) errNumber = CLng (Err.Number) Goto 0 error on error 'error 457, key p The move in the collection "... then some to associate the product with keywords if errNumber = 457 and keywords. Item (K) (Add) Add ProductCode ??? End if next j next I  

I am not bound by this method, so there is a better way to do this ... I am fine with him. Thanks in advance for the help.

You can use the object:

  sub-testing ( ) As the slow keywordColumn string, the productColumn string dim products string dim as in the form of a slow keyword in the form of a slow keyword in the form of slow keyword MyKey, P as the new dictionary keyword = CreateObject ("Scripting" After adding the Microsoft scripting Runtime reference to ". Dictionary") keywordColumn = "B" productColumn = 'A' ActiveSheet with maxRow = .Cells (.ows.Count, ProductColumn through each cell in the keyword column) .end (xlUp). 'Rows' for loop, ignoring column header for I = 2 maxRow' keyword Seamless comma goes so that they should be split () K = Split (.csells (i, keywordColumn) .Value, "") for Karma in every MyKey, then keywords.Exists (MyKey) Add key: = myKey, item: = New archive end if resume with errors on .csells (i, productColumn) Next keyword (MyKey) .Add item: = value, key:. = Error on CSTR (.Value) Goto 0 termination next with MyKey Next i ******* ********************************************************************************** **************************************************************************************************** ************* ********* Keywords for each key. Keighy Products = "" 'For each my keyword again for myKey keywords (myKey) products = products & amp; P & amp; "," Next "in the cells. Mi (i," D ") = myKey if the products are & lt; & gt;" then ". again. (I, "e") = left (products, lane (product) - 2) i = i + 1 ends with the next term  

RESULT:

note : I go to the Microsoft Scripting Runtime Library ( Tools-> Reference and Microsoft Scripting Runtime ). In that case, you can use it:

  New dictionaries like slow keywords  

Instead

  Moderate keywords Object set as Keyword = CreateObject ("Scripting.Dictionary")  

Referencing Library makes your code faster and intellisence for your keyword object Adds the feature.