Website Category Script using a Keyword Matching Formula

[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 so I can use the multi cell product description to determine which category that product should be assigned.

Category	Category Number	Keywords
Computers	1000	Computers, Computer, PC
Desktops	1100	Desktop, Desktops
Dual Core PC	1101	Dual Core
Quad Core PC	1102	Quad Core

<tbody>
</tbody>

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:

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

<tbody>
</tbody>

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:

<tbody></tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

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

I suspect this would probably be a script but as I am not familiar with VB/Excel scripting I need some help.

Many Thanks

J

 

[makingtrails]

I'm one step closer in solving this "impossible" task

I found the keyword solution here: http://www.mrexcel.com/forum/excel-...keywords-text-string-using-excel-formula.html thanks to Njimack but still need a little advise how to I can use the results to match up the product title & description to the categories

First of all I need to amend this UDF so it can search multiple cells instead of just one.

    Function Keywords(Words As Range, strText As Range) Dim c As Range For Each c In Words If InStr(1, strText, c, 1) > 0 Then Keywords = Keywords & ", " & c Next c If Keywords = 0 Then     Keywords = "-" Else: Keywords = Right(Keywords, Len(Keywords) - 2) End If End Function





</pre>

Usage: =Keywords($A$2:$A$26,B2)

Any help would be great!