+ Reply to Thread
Results 1 to 5 of 5

Return Category based on Keywords in a cell from array

  1. #1
    Registered User
    Join Date
    06-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    6

    Return Category based on Keywords in a cell from array

    Hi Experts,

    I have been having issue of resolving the above mentioned.

    Attached is the sample file.

    1. I was figuring on how to search keywords on the Resolution column and then return a category matching the keywords based on the array created in "Keywords" tab.

    For eg, If the keyword reset is found in the Resolution Column, the formula would search in the Keywords column and return the categories tag to Reset which is Password Reset.

    2. I am not sure if the formula can be expanded to work around this.

    =IF(SUM(COUNTIF(O2,{"*adapt*","*batt*","*cable*","*cartridge*","*LEGACY LAN*","*network*","*Patching*","*switch*","*dock*","*Print*","*domain*","*drum*","*fan*","*faulty*","*Bitlocker*","*TPM*","*recovery code*","*Remediat*","*mediat*","*recovery*","*jam*","*MB*","*mother*","*NCS*","*NIC*","*charge*","*refix*","*power unit*","*port*","*button*","*spacebar*","*fix*","*Commserve*","*noise*","*hard*","*flicker*","*replace*","*fuser*","*mark*","*dirty*","*smudge*","*kit*","*maintenance kit*","*toner*","*Paper Jam*","*vendor*","*Xerox*","*screen replace*"}))>0,"Break-Fix")))

    Thank you all in advance for your help.

    Best regards,
    Shad
    Attached Files Attached Files

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Return Category based on Keywords in a cell from array

    Hi Shad,

    I have done this other way round.. resolution description is now appearing in keywords sheet :-

    {=INDEX(Resolution!$A$1:$A$31,MIN(IF(ISNUMBER(SEARCH($A2,Resolution!$A$1:$A$31)),ROW(Resolution!$A$1:$A$31),"")),1)}

    See attached :- Keywords against Resolution.xlsx

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    06-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Return Category based on Keywords in a cell from array

    Hi Dilip,

    Appreciate your prompt reply.Thank you.

    However, it is not what is required.

    1. The formula needs to capture certain keywords from the Resolution Column in the Resolution tab. The keywords would take a reference from the list of keywords in Column A of the Keywords tab.

    The result would return the corresponding value in Column B of the Keywords Tab.

    I hope I am not confusing you.Heheheh

    Best regards,
    Muhamad Irshad

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Return Category based on Keywords in a cell from array

    hi Irshad, i found this similar thread awhile ago & thought it was interesting. but this idea isn't from me. modified it slightly to suit better your needs. see if it helps.
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Registered User
    Join Date
    06-11-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Return Category based on Keywords in a cell from array

    Quote Originally Posted by benishiryo View Post
    hi Irshad, i found this similar thread awhile ago & thought it was interesting. but this idea isn't from me. modified it slightly to suit better your needs. see if it helps.
    Hi Beni!

    I tried it...WORKED LIKE A CHARM!...woo hooo!!!
    Just what I was looking for...Thanks a million!!
    Great Great...U wont know how relieve I am....I will then work on it more then with additional keywords as the list gets longer....

    YESSSSSSSSSSSSAAAAAA!!!

    THanks to Dilip for the assistance..Great help guys!

    Appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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