+ Reply to Thread
Results 1 to 6 of 6

Automatic category selection as seen in Yahoo Answers

  1. #1
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Automatic category selection as seen in Yahoo Answers

    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.

  2. #2
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Automatic category selection as seen in Yahoo Answers

    Please note I have also posted this request elsewhere:

    http://www.mrexcel.com/forum/showthread.php?t=586074

    http://www.ozgrid.com/forum/showthread.php?t=159150
    Last edited by robnet77; 10-18-2011 at 11:06 AM.

  3. #3
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Automatic category selection as seen in Yahoo Answers

    duplicate of previous post
    Last edited by robnet77; 10-17-2011 at 12:08 PM. Reason: in fact a duplicate post

  4. #4
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Automatic category selection as seen in Yahoo Answers

    from user Wigi:

    Here's a solution. See attachment for a formula approach:

    =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.

  5. #5
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Automatic category selection as seen in Yahoo Answers

    I looked at your solution Wigi, 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.

  6. #6
    Registered User
    Join Date
    10-17-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Automatic category selection as seen in Yahoo Answers

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1