Keyword matching and multiple VLOOKUP

[makingtrails]

Hi Guys,

I am looking to automatically assign products to a relevant categories on my website and I am working on a spreadsheet to help me do this fast and sufficiently

Each category is has a four digit number assigned to it and categories are limited to 3 (Primary, Secondary & Tipperary)

For example:

Computers - 1000 - Desktops - 1100 - Dual Core PC - 1101
Computers - 1000 - Desktops - 1100 - Quad Core PC - 1102

So as you can see you can tell a Dual Core PC is in the Computer & Desktop categories just by looking at the first two digits. The last two digits are the category number. 01, 02, 03 etc.

What I wish to do is associate keywords to the category determine which category that product should be assigned.

Product ID	Product Title	Product Description	Product Spec1	Product Spec2
1	HP Desktop PC	HP Desktop PC's are computers	Intel Dual Core Processor	500GB HDD
2	HP Desktop PC	HP Desktop PC's are computers	Intel Quad Core Processor	1TB HDD

Keywords assigned to category 1101 are Computers, Computer, PC, Desktop, Dual Core.
Keywords assigned to category 1102 are Computers, Computer, PC, Desktop, Quad Core.

And the spreadsheet looks like this:



The script/sum would look at all the cells and match the highest count of keywords to that particular category and populate additional fields called Cat1, Cat2 & Cat3. Therefore the final spreadsheet would look like this:

Product ID	Product Title	Product Description	Product Spec1	Product Spec2	Cat1	Cat2	Cat3
1	HP Desktop PC	HP Desktop PC's are computers	Intel Dual Core Processor	500GB HDD	1000	1100	1101
2	HP Desktop PC	HP Desktop PC's are computers	Intel Quad Core Processor	1TB HDD	1000	1100	1102

Anyone have any idea's how this can be implemented? possibility with the use of VLOOKUP or MATCH commands?

I believe a vlookup would be fine but there's was only 1 value to match but as it's multiples it would need to prioritise based on the number of keywords matched and not just the first match found.

Thanks

J

http://www.makingtrails.com/keyword-test.xlsx

[dilipandey]

Hi Makingtrails,

Welcome to the forum.

I would suggest you to have a keyword column in sheet "Product-DB" as well, before column Cat1, which will have the keywords extract from columns B, C,D and basis that we can apply lookup functions... This column can fetch keywords using some formulas but for that can you provide some more rows or data? If sounds interesting , post updated workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[makingtrails]

Hi Makingtrails,

Welcome to the forum.

I would suggest you to have a keyword column in sheet "Product-DB" as well, before column Cat1, which will have the keywords extract from columns B, C,D and basis that we can apply lookup functions... This column can fetch keywords using some formulas but for that can you provide some more rows or data? If sounds interesting , post updated workbook. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Hi,

I have updated the workbook to include a macro which matches product keywords to a keyword worksheet. All I need to do now is correctly allocate the matches to the categories also in a worksheet.

Updated - http://www.makingtrails.com/keyword-test.xlsm

Thanks for your help!

[dilipandey]

Hi MakingTrails,

Keywspec2 is not there in sheet "Categories" .. I was looking for to have a column of keywords similar to column D of sheet "Categories" and then a simple vlookup can help Thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

[makingtrails]

Hi MakingTrails,

Keywspec2 is not there in sheet "Categories" .. I was looking for to have a column of keywords similar to column D of sheet "Categories" and then a simple vlookup can help Thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

it doesn't have to be, keywdesc (L$), keywspec1 (M$) & keywspec2 (N$) are merged to create keywords F$ which is Keywords (D$) in the Categories worksheet.