+ Reply to Thread
Results 1 to 5 of 5

Keyword Matching & Categories

  1. 04-04-2014, 06:16 PM #1
    leeandoona
    leeandoona is offline
    Registered User
    Join Date
    04-04-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Keyword Matching & Categories

    Hi everyone, this is my very first post to this forum, please accept my apologies if this is a dumb question. I'm trying to teach myself some tricks in excel (using 2003) but I've hit the wall!

    I'm trying to look at a range of text data on one sheet and where I find matching keywords from a list on another sheet I want to get/show the appropriate keyword category so that I'm then able to upload a huge list of products to our website in their correct website categories. It seems simple enough but I can't seem to figure out a way to use VLOOKUP, HLOOKUP, LOOKUP or MATCH/FIND/SEARCH to get this to work properly. I got real close with LOOKUP but no cigar!

    I've attached my workbook which shows a practical example of what I'm working with along with an example in rows 2 and 3 of sheet 1 (Example Stock Sheet) of what I'm trying to do. My actual data is substantially larger in volume of course, with approx 30,000 rows of text (Product Title) which contains Keywords that all belong to a website category (Category Path). I have my keywords listed (sheet 2 - Just the Keywords) and I also have them 'Categorised' with a unique code (Sheet 3 - Keywords & Web Category Path). I can see there may be a few ways to crack this but don't seem to be able to see the wood through the trees as we say here. Any advice on this would be hugely helpful for me.

    Thanks
    Lee
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-04-2014, 07:13 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Keyword Matching & Categories

    Set your table up like shown, then a LOOKUP() formula will match the categories for you. NOt sure what the performance will be like if the KEYWORDS were 30k rows, but if its the first sheet that's 30k rows, then this will be fine.

    =LOOKUP(2, 1/(ISNUMBER(SEARCH('Just the Keywords'!$A$2:$A$54, $A2))), 'Just the Keywords'!$B$2:$B$54)
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 04-05-2014, 02:31 PM #3
    leeandoona
    leeandoona is offline
    Registered User
    Join Date
    04-04-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Keyword Matching & Categories

    Honestly I don't know where to begin in thanking you for this! Simply amazing and would have taken me a VERY long time to work it out that way! Thank you so very much indeed. This is possibly the most impressive bit of assistance I've ever experienced with anything! So thanks again. No really...thank you!!!

    The first sheet is the large amount of info with only a slightly larger expansion of Keywords and I've tried this out and it works an absolute dream, even in 2003 on my clunky vista laptop which is always slow with excel LOOKUP. What a really cool and slick trick!

    Thanks again. I owe you one!
    Reply With QuoteRegister To Reply
  4. 04-05-2014, 09:23 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Keyword Matching & Categories

    Something to keep in mind... this LOOKUP() trick is making an array of all the keywords that match within your text string and then giving you back the matching value from column B of the keywords. The hope is that the array will always only have ONE actual match. But if that's not the case, the formula will always return the LAST match from the keyword list.

    So, I'd make sure the keyword list was sorted from least important (top) to most important (bottom).

    Cheers.
    Reply With QuoteRegister To Reply
  5. 04-09-2014, 08:29 AM #5
    leeandoona
    leeandoona is offline
    Registered User
    Join Date
    04-04-2014
    Location
    Essex, England
    MS-Off Ver
    Excel 2003
    Posts
    3

    Cool Re: Keyword Matching & Categories

    Ah! Gotchya. Okay I see how that's working. Thanks that's really helpful.
    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. Keyword Matching
    By sathishkm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-02-2013, 11:50 AM
  2. keyword matching in a cell(compliated)
    By hk4kim in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2013, 05:30 PM
  3. Keyword matching and multiple VLOOKUP
    By makingtrails in forum Excel General
    Replies: 4
    Last Post: 12-07-2012, 10:57 AM
  4. keyword, matching and breaking up??
    By SarahPintal in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-17-2010, 07:47 PM
  5. Matching categories and Summing
    By Pasha81 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-10-2009, 01:52 PM

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