+ Reply to Thread
Results 1 to 14 of 14

IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

  1. #1
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    =IF(ISNUMBER(SEARCH("A*P*",K6)),"Pear",IF(ISNUMBER(SEARCH("A*L*",K6)),"Leaf",""))


    In column K -- starting with K6 data and down I have the above formula(s) to identify which are "Pear" and which are "Leaf" displaying a result in Column AH.

    Now I am stuck because I have to add another identifier to the same formula and display the result "orange" in the same AH Column of cells.

    Basically I need something that is not "A*P* nor "A*L*" and identify it as "Orange"


    HOpefully that makes some sense., I am new here and trying to take excel to another level.....thank you for all help and direction in advance...

    -rt

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    richtree,

    Welcome to the forum!
    Here's one way...
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Just replace the "" with Orange:

    =IF(COUNT(SEARCH("A*P*",K6)),"Pear",IF(COUNT(SEARCH("A*L*",K6)),"Leaf","Orange"))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Quote Originally Posted by Tony Valko View Post
    Just replace the "" with Orange:

    =IF(COUNT(SEARCH("A*P*",K6)),"Pear",IF(COUNT(SEARCH("A*L*",K6)),"Leaf","Orange"))
    Tiger, Tony -- thanks for your responses......Tony's seemed easier., and is working except I forgot to notice one item....

    Its not just A*P* and A*L* --- but it must begin with A* not just contain that string......can I input a LEFT command somewhere ? ? this is the only item holding this up...


    thanks again for both your help ? can anyone help with that ?


    -rt

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Like this...

    =IF(COUNT(SEARCH("A*P*",K6)),"Pear",IF(COUNT(SEARCH("A*L*",K6)),"Leaf",IF(LEFT(K6)="A","Orange","")))

  6. #6
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    =IF(COUNT(SEARCH("A*P*",K6)),"Pear",IF(COUNT(SEARCH("A*L*",K6)),"Leaf","Orange"))

    I am working with this one --- I think I may have confused you......


    I need the Pear and Leaf to confirm the A in the "LEFT" position and all other not Leaf or Pear to be "Orange"

    Does that make sense ?

    thanks again

    -rt

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Does that make sense ?
    At this point I'm kind of

    Can you post SEVERAL representative samples of the data and tell us what results you expect?

  8. #8
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Quote Originally Posted by Tony Valko View Post
    At this point I'm kind of

    Can you post SEVERAL representative samples of the data and tell us what results you expect?
    Basically it is working except for theis

    The Pears should be model numbers that start

    A****P**** etc.....


    The Leaf should be model numbers that start

    A****L**** etc.....


    but !!!!!!!

    there are models that are "Orange" that are coming up as "Pear" --- for example

    YIRAEDWPZ <---- This has an A*P* string in it but it should only be "Pear" with the A as the first character.......anything else without a A in first character should automatically be "Orange"

    clear ?

    thanks again !!!!

  9. #9
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    and same true for Leaf as above... Only a Leaf with A*L* where A is first character...

    thanks !!!!!!!!!!!!


    *Note(see above post as well)

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Maybe this...

    =IF(COUNTIF(K6,"A*P*"),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange"))

    An empty cell will generate an "Orange" result.

    This version will account for empty cells:

    =IF(K6="","",IF(COUNTIF(K6,"A*P*"),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange")))

  11. #11
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Quote Originally Posted by Tony Valko View Post
    Maybe this...

    =IF(COUNTIF(K6,"A*P*"),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange"))

    An empty cell will generate an "Orange" result.

    This version will account for empty cells:

    =IF(K6="","",IF(COUNTIF(K6,"A*P*"),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange")))
    Tony,

    Firstly, I really appreciate your help. Secondly, I just found out there are two more pieces of criteria I have to add to these metrics to have clear detail.

    #1 in addition to the logic above, a pear could also be defined a model beginning as "AH".

    #2 in addition to Pear, Orange, and Leaf --- I have to also identify any model that begins as DMC in first three characters as "double"


    Can these be added in ? I am trying to understand these formulas but it seems so hard to find where to start and which one to use.

    thanks again

    rich

  12. #12
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    I think this accounts for all the conditions:

    =IF(K6="","",IF(LEFT(K6,3)="DMC","Double",IF(SUM(COUNTIF(K6,{"A*P*","AH*"})),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange"))))

  13. #13
    Registered User
    Join Date
    11-13-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Quote Originally Posted by Tony Valko View Post
    I think this accounts for all the conditions:

    =IF(K6="","",IF(LEFT(K6,3)="DMC","Double",IF(SUM(COUNTIF(K6,{"A*P*","AH*"})),"Pear",IF(COUNTIF(K6,"A*L*"),"Leaf","Orange"))))
    Thanks, that worked perfectly...Now I need to understand all this to be able to know when to pick CountIf , or ISNUMBER, SEARCH, VLOOK, ETC....

    it seems so hard for the average excel user to get from good to expert.....I will change to solved....please feel free to point me in the right direction for moving from the average excel analyst to a expert...thanks again

    rich

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IF -- ISNumber (Search -- help when there is a variable to be excluded and identified

    Quote Originally Posted by richtree View Post
    Thanks, that worked perfectly
    Good deal!

    please feel free to point me in the right direction for moving from the average excel analyst to a expert
    That's pretty easy, actually!

    Spend a few hours EVERY day in forums like this one reading the posts and studying the solutions. Do tons of experimentation.

    In about 4-5 years you'll be an Excel Guru!

+ 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