Automatic category selection as seen in Yahoo Answers

[robnet77]

Hi,

I have a list of keywords with related category of incidents they belong to.

I would like to create an automatic category selection in Excel based on the keywords entered by a user.

For instance, if a user enters "SAP password reset" I want to match those three words (SAP+password+reset, separately) with a database like this:

SAP CAT 1
reset CAT 1
password CAT 1
Windows CAT 2
password CAT 2
reset CAT 2
inquiry CAT 3
install CAT 4

... so that I get the relevance of those keywords for each category, ie:

CAT 1 100%
CAT 2 66%
CAT 3 0%
CAT 4 0%

If possible at all, I would like to do this with only functions and pivot tables, but I'm willing to use macros if it's necessary.

I have tried using VLOOKUP but it only gives me the first occurrence of a keyword, whereas a keyword (eg. SAP) can be present in multiple categories.

 

[wigi]

=SUM(--ISNUMBER(MATCH($A$14:$A$23&C14,$A$1:$A$8&$B$1:$B$8,0)))/COUNTIF($B$1:$B$8,C14)

Array-entered.

 

[robnet77]

thanks Wigi, I'll look into it, also as you mentioned in another forum, I want to advise everybody that I also posted the same request on different forums as I need urgent help:

http://www.ozgrid.com/forum/showthread.php?t=159150

http://www.excelforum.com/excel-gen...egory-selection-as-seen-in-yahoo-answers.html

 

[robnet77]

I looked at your solution, it works well but the example I provided is a simplified version of the final database I'm creating (still in progress).

- my database will have about 200 categories and I don't want to show all of them, as it would take ages for the users to see which categories fit best, basically I'd like to only display categories that have a value bigger than 0%, if possible in descending order...

eg.

CAT 24 = 92%
CAT 1 = 56%
CAT 82 = 12%
- also, I've realized that the efficiency of the engine can be improved by giving each keyword a "weight" (relevancy) according to how frequently it appears within a category.

Word Category relevancy

SAP CAT 1 68.1 (out of 100 incidents logged, the word "SAP" appears 68.1 times in CAT 1)
reset CAT 1 60.2
password CAT 1 45.8
Windows CAT 2 75.5
password CAT 2 51.3
reset CAT 2 36.2
inquiry CAT 3 48.9
on CAT 3 21.1
install CAT 4 74.0
software CAT 4 31.1

Can anyone help? Thanks in advance.

 

[robnet77]

I may have finished the project, see picture in the ozgrid forum, however if anyone has a clean way to do this, please let me know, my Excel is rather rough...

I used VLOOKUP to find unique values (concatenated: keyword & category) and display relevancy factor, then used RANK to sort values, then used ISERROR and ISNUMBER to only show values bigger than zeros for categories and relevancy-points scored.