+ Reply to Thread
Results 1 to 5 of 5

Help with type of formula required

  1. #1
    Registered User
    Join Date
    10-24-2006
    Posts
    4

    Help with type of formula required

    I have a column containing text in each cell, sometimes with numerous words.

    I have a number of categories and a list of key words for each of these categories. I would like to apply one of these categories to each cell (say in the adjacent cell) if one of the keywords appears in any cell.


    For example

    I have two categories, fruit and vegetables.
    Under each category I have listed all the possible types of fruit and veg (this would be in a separate sheet).

    In another sheet in column A I have lots of cells all containing text.

    e.g. one cell may be ‘I went to the shop and bought some apples’. I would like to write a formula that could then return ‘Fruit’ in the cell adjacent to this one.

    What sort formula do I need for this?

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Help with type of formula required

    If you list all fruits and name the range Fruit then you can use this formula in B1, where data to search is in A1

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit","")

    with another range called Veg.....

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit",IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg",""))

    Note: this won't accommodate both Fruit and Veg in one cell, if you want that then try

    =TRIM(IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg",""))

    ....also the fruit won't need to be distinct words. If Grape is listed as a fruit you'll get a match with Grapefruit......although you can change the formulas to accommodate that too.......

  3. #3
    Registered User
    Join Date
    10-24-2006
    Posts
    4

    Re: Help with type of formula required

    Thanks daddy long legs.

    I am however going to be looking to return a result from multiple ranges. How do I change the second formula in your response to if, for example, I have third and fourth ranges called ‘Meats’ and ‘Breads’?

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Help with type of formula required

    You should be able to keep concatenating SUMPRODUCT formulas as per my example, e.g.

    =TRIM(IF(SUMPRODUCT(--ISNUMBER(SEARCH(fruit,A1))),"Fruit"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(Veg,A1))),"Veg","")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(meats,A1))),"Meats"," ")&" "&IF(SUMPRODUCT(--ISNUMBER(SEARCH(breads,A1))),"Breads",""))

    Are you expecting a single cell to contain values from more than one category?

+ 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