+ Reply to Thread
Results 1 to 8 of 8

IF formula that checks several values and several keywords

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Post IF formula that checks several values and several keywords

    Hi there,

    I'm trying to group a database of over 20,000 products into 43 group types. Several groups sit within departments, which I have already manually specified for each of the products.

    Given the time it would take to manually group these products, I'm trying to figure a formula that first checks the department number (between 1 and 9), then checks for keywords in the item description to apply a group number to it.

    For instance, if a product was called 'Vitamin C Tablets 100s' it would already be in department 1 for 'vitamins and supplements' so the formula would check this, then check against a list of keywords I have in order to assign the correct group ID, so for instance, it would check against keywords like 'capsules' 'tablets' 'tablet' 'caps' 'capsule' 'tabs' and if it matches any of these, it would display a number '1' in the cell to represent the 'capsule/tablets' group.

    I know this requires a nested IF formula, but I have been trying to create one that works all morning with no luck. Any ideas would be massively appreciated.

    I have attached a sample spreadsheet for how the data should look.

    Thanks in advance Sample Sheet.xlsx

  2. #2
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: IF formula that checks several values and several keywords

    Hi
    Can you give me a list of your keywords. You may not be able to use an IF statement as there is a maximum number of 7 nested IF statements that can be used in Excel. However you can use VLOOKUP that will give you the same result and then just copy the results of the VLOOKUP column and paste values only to achieve what you are after.
    Please provide a larger sample and I will put together a sample for you with instructions.
    Also let me have a list of your departments.
    Tony

  3. #3
    Registered User
    Join Date
    07-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula that checks several values and several keywords

    Hi Tony,

    Thanks for the quick response.

    Department list is as follows:

    1. Vitamins and Supplements
    2. Ambient Foods
    3. Tea Coffee Alternatives
    4. NOT USED
    5. Chilled
    6. Frozen
    7. Snacks and Drinks
    8. Household
    9. Beauty and Hygiene

    Groups are as follows, with keywords in brackets:

    1. Capsules/Tablets (caps, tabs, tablets, capsules, tablet, capsule, vegicaps, vegecaps)
    2. Oils (oil)
    3. Liquids and essences (essence, formula, tincture)
    4. Pre packed goods (Dried, nuts, fruit, seeds)
    5. Cereals (oats, muesli, flakes, puffs)
    6. Tinned Goods (tin, tinned)

    . . . and so on.

    As you can see, it was necessary to involve the department number in this formula as the keywords can be quite generic and probably exist in some products that are not actually related to the group that the keyword sits within, so the department reference ensures the product sits within a broader category before the keyword reference takes place.

    Look forward to your assistance and formula suggestions.

    Gavin

  4. #4
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: IF formula that checks several values and several keywords

    Hi Gavin
    I am struggling a little with the sample data provided. Can you provide a larger sample data set with the Department Numbers contained therein. I have tried a couple of things but no joy - YET! .
    Tony

  5. #5
    Registered User
    Join Date
    07-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula that checks several values and several keywords

    Hi Tony,

    Please find a larger data sample attached with a range of dept numbers in there.

    Hope this is sufficient for you to crack the formula! I'm still having no luck on my side.

    GavinSample Sheet.xlsx

  6. #6
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: IF formula that checks several values and several keywords

    Hi Gavin
    I think I have cracked it. See below for individual formula which will need to change for each Group, I am sure you can work it out from here:

    Department 1 =IF(COUNT(SEARCH({"caps","tablets","tabs","capsules","tablet","capsule","vegicaps","vegecaps"},B24)),"1")

    The group number at the end of the formula depending on the criteria.

    Hope this helps.
    Good luck.
    Tony
    Last edited by ARGK; 07-31-2013 at 05:40 AM.

  7. #7
    Registered User
    Join Date
    07-30-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: IF formula that checks several values and several keywords

    Tony, you are a star!

    That worked perfectly.

    Had to break it down to 2 department checks at a time, because the formula didn't accommodate more than this. However, a quick Find and Select > Go to special > then check the 'Formulas and Logicals' radio buttons to select the FALSE results and re-apply the formula for another 2 departments and so on - it worked a treat!

    Cheers again Tony, much appreciated.

    Gavin

  8. #8
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: IF formula that checks several values and several keywords

    Thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula which checks whether a string is found in a range and checks 2 criteria
    By liranbo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-30-2012, 05:28 PM
  2. Formula to determine day of week checks will be cut
    By shanshine in forum Excel General
    Replies: 13
    Last Post: 09-28-2011, 12:21 PM
  3. formula that checks a cell for equal or less value
    By NakedFury in forum Excel General
    Replies: 3
    Last Post: 12-05-2010, 05:56 AM
  4. Formula question-there are safety checks.
    By Gypsiyee in forum Excel General
    Replies: 6
    Last Post: 06-19-2007, 11:15 AM
  5. Formula/function that checks for a set of values.
    By Memnok in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-24-2006, 05:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1