+ Reply to Thread
Results 1 to 7 of 7

Categorize text with keywords.. Pick longest match first

  1. 07-19-2019, 10:44 PM #1
    gogogadget99
    gogogadget99 is offline
    Registered User
    Join Date
    07-19-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    4

    Categorize text with keywords.. Pick longest match first

    Greetings,

    I am having trouble finding answers online for the following problem i am working on.
    So basically i am trying to find a solution to pick the longest keyword out of many keywords from the solution provided on
    exceljet net/formula/categorize-text-with-keywords

    Expense Category Keyword Categories
    23 Shell Transportation Shell Transportation
    Netflix 445566 Entertainment Netflix Entertainment
    Shell LA Transportation coned Utilities
    Shell 34223 Transportation Shell LA Due to from Owners
    9988 ConEd dsnkjsd Utilities
    jkdsh Netflix Entertainment
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 07-20-2019, 01:23 AM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Categorize text with keywords.. Pick longest match first

    Please try at B2
    =Lookup(1,0/search(E$2:E$5,A2),F$2:F$5)

    Sort column E from A-Z
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 07-20-2019, 08:46 AM #3
    gogogadget99
    gogogadget99 is offline
    Registered User
    Join Date
    07-19-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    4

    Re: Categorize text with keywords.. Pick longest match first

    The point is to do it without constantly sorting manually as the keyword list grows.
    Reply With QuoteRegister To Reply
  4. 07-20-2019, 09:07 AM #4
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Categorize text with keywords.. Pick longest match first

    Ok
    =INDEX(E:E,MOD(AGGREGATE(14,6,SEARCH($E$2:$E$5,A2)*LEN($E$2:$E$5)*10^5+ROW($E$2:$E$5),1),10^5))
    Reply With QuoteRegister To Reply
  5. 07-20-2019, 04:17 PM #5
    gogogadget99
    gogogadget99 is offline
    Registered User
    Join Date
    07-19-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    4

    Re: Categorize text with keywords.. Pick longest match first

    Very close... :D

    Its displaying the correct keyword instead of the categories.

    Expense Category Keyword Categories
    23 Shell Shell Shell Transportation
    Netflix 445566 Netflix Netflix Entertainment
    Shell LA Shell LA coned Utilities
    Shell 34223 Shell Shell LA Due to from Owners
    9988 ConEd dsnkjsd coned
    jkdsh Netflix Netflix
    Reply With QuoteRegister To Reply
  6. 07-21-2019, 05:03 AM #6
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Categorize text with keywords.. Pick longest match first

    =index(f:f,mod(aggregate(14,6,search($e$2:$e$5,a2)*len($e$2:$e$5)*10^5+row($e$2:$e$5),1),10^5))
    Reply With QuoteRegister To Reply
  7. 07-23-2019, 02:11 PM #7
    gogogadget99
    gogogadget99 is offline
    Registered User
    Join Date
    07-19-2019
    Location
    new york
    MS-Off Ver
    2016
    Posts
    4

    Re: Categorize text with keywords.. Pick longest match first

    Thank you, Works perfectly !
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to match longest to shortest
    By javierzgaray in forum Excel General
    Replies: 3
    Last Post: 04-07-2016, 08:17 AM
  2. Categorize Cell Value by Keywords
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-23-2015, 12:26 PM
  3. Categorize Cell Value by keywords
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2015, 11:28 AM
  4. How do I categorize a list/column into separate ones by finding keywords?
    By MoldyBread in forum Excel General
    Replies: 3
    Last Post: 07-28-2014, 08:00 PM
  5. [SOLVED] HDI - Return multiple keywords in a text column based on a set of available keywords
    By zneiley in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-25-2013, 01:32 PM
  6. Find word(s) within a text string that match a list of keywords. (VBA)
    By nigellegg in forum Excel General
    Replies: 0
    Last Post: 07-11-2012, 06:05 AM
  7. [SOLVED] Using a discrete range of keywords, search a text string for match
    By Ian99 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-24-2012, 11:40 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1