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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Category
[/TD]
[TD]Category Number
[/TD]
[TD]Keywords
[/TD]
[/TR]
[TR]
[TD]Computers
[/TD]
[TD]1000
[/TD]
[TD]Computers, Computer, PC
[/TD]
[/TR]
[TR]
[TD]Desktops
[/TD]
[TD]1100
[/TD]
[TD]Desktop, Desktops
[/TD]
[/TR]
[TR]
[TD]Dual Core PC
[/TD]
[TD]1101
[/TD]
[TD]Dual Core
[/TD]
[/TR]
[TR]
[TD]Quad Core PC
[/TD]
[TD]1102
[/TD]
[TD]Quad Core
[/TD]
[/TR]
</tbody>[/TABLE]


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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Product Title
[/TD]
[TD]Product Description
[/TD]
[TD]Product Spec1
[/TD]
[TD]Product Spec2
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]HP Desktop PC
[/TD]
[TD]HP Desktop PC's are nice computers
[/TD]
[TD]Intel Dual Core Processor
[/TD]
[TD]500GB HDD
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]HP Desktop PC
[/TD]
[TD]HP Desktop PC's are nice computers
[/TD]
[TD]Intel Quad Core Processor
[/TD]
[TD]1TB HDD
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody></tbody>[/TABLE]
[TABLE="width: 700"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 700"]
<tbody>[TR]
[TD]Product ID
[/TD]
[TD]Product Title
[/TD]
[TD]Product Description
[/TD]
[TD]Product Spec1
[/TD]
[TD]Product Spec2
[/TD]
[TD]Cat1
[/TD]
[TD]Cat2
[/TD]
[TD]Cat3
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]HP Desktop PC
[/TD]
[TD]HP Desktop PC's are nice computers
[/TD]
[TD]Intel Dual Core Processor
[/TD]
[TD]500GB HDD
[/TD]
[TD]1000
[/TD]
[TD]1100
[/TD]
[TD]1101
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]HP Desktop PC
[/TD]
[TD]HP Desktop PC's are nice computers
[/TD]
[TD]Intel Quad Core Processor
[/TD]
[TD]1TB HDD
[/TD]
[TD]1000
[/TD]
[TD]1100
[/TD]
[TD]1102
[/TD]
[/TR]
</tbody>[/TABLE]

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!