+ Reply to Thread
Results 1 to 15 of 15

if(isnumber(search - how to not include matching phrases

  1. #1
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    if(isnumber(search - how to not include matching phrases

    I have a list of values I need to sort into groups, and the easiest way to do this seems to be to be using if(isnumber(search so I can group values according to their topics. For example:

    red apples
    green apples
    fairtrade bananas
    white grapes
    grapefruits
    dry cider
    apple cider

    I need to put these into groups:

    Apples
    Bananas
    Grapes
    Grapefruits
    Cider

    However if I use if(isnumber(search it will put grapefruit into the Grapes category and Cider into the Apple category. Is there a way round this so that grapefruit will always be in grapefruit and cider will always be in cider?

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: if(isnumber(search - how to not include matching phrases

    make manualy a table for all options so you can use VLookup to determine the categorie.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    make manualy a table for all options so you can use VLookup to determine the categorie.
    This isn't really what I'm looking for. I need to categorise the cells and I'll still have issues with items appearing in the wrong group. Thanks for the input though.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    Hi.

    What you're asking is quite straightforward, though it would be helpful first if you could attach a workbook with an example plus your expected results.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: if(isnumber(search - how to not include matching phrases

    Based on your example, I don't see the problem since 'grapes' and 'apples' don't appear in the other items.
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    Based on your example, I don't see the problem since 'grapes' and 'apples' don't appear in the other items.
    Whoops bad example. Let's change that to:

    red apple
    green apple
    fairtrade bananas
    white grape
    grapefruits
    dry cider
    apple cider

    I can't put the exact data I'm working with as it's sensitive information, but it's the same concept. I would like it displayed as 2 columns, so the list of fruits in column A and their correct category in column B.

    Thanks.

  7. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    Quote Originally Posted by jceg316 View Post
    I can't put the exact data I'm working with as it's sensitive information
    Of course, but can you not upload a small, mocked-up sheet which illustrates the same concept, just as in your fruit example here?

    Regards

  8. #8
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    Fruit Ex.xlsx

    There are 2 tabs.

  9. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    Thanks.

    The fact that some of your categories are in the plural, and some singular, e.g. "Apples", but "Beer", complicates matters.

    Is this not something which can be standardised?

    Edit: this would ideally also need to be the case with the items in the Products list.

    Regards
    Last edited by XOR LX; 02-13-2015 at 06:50 AM.

  10. #10
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    I wish it were more simple but this does reflect what my real data is like.

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    Ok. Can you at least guarantee that the only form of plural nouns being used will be that which involve the addition of an "s" to the singular?

    Regards

  12. #12
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    Yes that's the case. thanks for helping out.

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    This is not 100% rigorous, though try this in B2 of the Products sheet:

    =LOOKUP(1,0/MMULT(N(ISNUMBER(SEARCH(" "&LEFT(Categories!$A$1:$A$7,LEN(Categories!$A$1:$A$7)-{0,1})&" "," "&Products!A2&" "))),{1;1}),Categories!$A$1:$A$7)

    Copy down as required.

    The reason I say that it's not foolproof is that it performs a search for each of the strings in the Categories tab twice: once for the word as it stands, and once for that word minus it's final letter.

    This is an attempt to resolve the issue I mentioned. However, there is a very small chance that it could lead to incorrect results. For example, if both "Pear" and "Pea" were contained within the Categories list, then an entry in the Products list of "Green Pea" could well return "Pear".

    Apologies - but I do not know of a simple way to resolve this potential issue.

    Regards

  14. #14
    Registered User
    Join Date
    09-12-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    33

    Re: if(isnumber(search - how to not include matching phrases

    Thanks so much for the help, however when I apply it to my spreadsheet it doesn't seem to work. Not sure why as all references are updated but it just returns the dreaded #N/A. I'm not sure what else I can show without giving out the data I'm working with, which unfortunately I can't do.

  15. #15
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,600

    Re: if(isnumber(search - how to not include matching phrases

    And I assume that you amended the ranges to meet your actual set-up accordingly?

    And that you've also verified that there is actually one such Category which contains at least one of the words for the Product being queried?

    Perhaps you can post the version of the formula as adapted by you for one cell? And then say why you think that #N/A is not the correct result, e.g. "Cell A2 contains "red apple" and cell A6 in my Categories list contains "Apples", so I would expect a result of "Apples" here"?

    Regards

+ 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] Matching using IF / ISNUMBER
    By jilaba in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-07-2013, 03:56 PM
  2. If, or, isnumber, search
    By reb2u in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-15-2012, 10:53 AM
  3. Help with list of search phrases
    By plumfin in forum Excel General
    Replies: 5
    Last Post: 01-03-2012, 03:03 PM
  4. Compare lists of phrases in two workbooks and import only missing phrases
    By Fireblejd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 06:59 AM
  5. How do I search multiple separate phrases at once?
    By Dcrader in forum Excel General
    Replies: 4
    Last Post: 01-02-2009, 02:09 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