+ Reply to Thread
Results 1 to 12 of 12

Is there IF ANY function in Excel like in GBI???

  1. 01-25-2018, 07:53 AM #1
    JuNova
    JuNova is offline
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Exclamation Is there IF ANY function in Excel like in GBI???

    I have two columns with text values - categorisations of priority: P1, P2, P2-high or N/A.
    I want to create a formula that checks both columns for one value and if that value exists populates my new column with that value.
    It would look something like rounding up to higher number but with text values.

    What I am looking for is something like this:

    If any cell in both columns has got "P1" then "P1", if any cell in both columns has got "P2-high" and no cell in both columns is "P1" then "P2-high", if any cell in both columns has got "P2" and no cell in both columns is "P1" or "P2-high" then "P2"

    I can do the above in GBI but not in excel. HELP!
    Reply With QuoteRegister To Reply
  2. 01-25-2018, 08:21 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Is there IF ANY function in Excel like in GBI???

    Maybe

    =IF(OR(A2="P1",A2="P1"),"P1",IF(OR(A2="P2-HIGH",A2="P2-HIGH"),"P2-HIGH",IF(OR(A2="P2",A2="P2"),"P2","Not Defined")))

    but not all options have been defined yet...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 01-25-2018, 08:27 AM #3
    JuNova
    JuNova is offline
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Re: Is there IF ANY function in Excel like in GBI???

    All the other options would be with N/A values and I do not mind for N/A or "None" to show in that column.
    Reply With QuoteRegister To Reply
  4. 01-25-2018, 08:30 AM #4
    samba_ravi
    samba_ravi is online now
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Is there IF ANY function in Excel like in GBI???

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  5. 01-25-2018, 08:38 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Is there IF ANY function in Excel like in GBI???

    So... does my formula in post 2 work, modified to

    =IF(OR(A2="P1",A2="P1"),"P1",IF(OR(A2="P2-HIGH",A2="P2-HIGH"),"P2-HIGH",IF(OR(A2="P2",A2="P2"),"P2","NA")))
    Reply With QuoteRegister To Reply
  6. 01-25-2018, 08:38 AM #6
    sandy666
    sandy666 is offline
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Is there IF ANY function in Excel like in GBI???

    @Glenn
    Or ( a2 ="p1", a2 ="p1" ) ???
    Reply With QuoteRegister To Reply
  7. 01-25-2018, 08:41 AM #7
    Sam Capricci
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: Is there IF ANY function in Excel like in GBI???

    I was thinking along the lines of something like this ... =if(AND(vlookup("P1",A:A,1,false)="P1",vlookup("P1",b:b,1,false)="P1"),"P1",if(and(vlookup("P2",A:A,1,false)="P2",vlookup("P2",b:b,1,false)="P2"),"P2" etc... but given the part "if any cell in both columns has got "P2-high" and no cell in both columns is "P1"" to have it in if vlookup formulas it would have to be in the correct order in order to have the formula quit when the correct sequence has been obtained.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  8. 01-25-2018, 08:43 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Is there IF ANY function in Excel like in GBI???

    Oh b&*$%$£$^% what a moron....

    =IF(OR(A2="P1",B2="P1"),"P1",IF(OR(A2="P2-HIGH",B2="P2-HIGH"),"P2-HIGH",IF(OR(A2="P2",B2="P2"),"P2","NA")))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-25-2018, 08:59 AM #9
    JuNova
    JuNova is offline
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Re: Is there IF ANY function in Excel like in GBI???

    In a situation where I had P1 and N/A it returned N/A but it should return P1.
    Reply With QuoteRegister To Reply
  10. 01-25-2018, 09:04 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Is there IF ANY function in Excel like in GBI???

    Try the formula at Post 8. Sandy pointed out that i'd made a mess of earlier versions!!
    Reply With QuoteRegister To Reply
  11. 01-25-2018, 09:29 AM #11
    JuNova
    JuNova is offline
    Registered User
    Join Date
    02-15-2016
    Location
    London, UK
    MS-Off Ver
    14
    Posts
    32

    Re: Is there IF ANY function in Excel like in GBI???

    My N/A values were an error of no value found #N/A. Once I change those to N/A the formula works!

    THANK YOU!!!
    Reply With QuoteRegister To Reply
  12. 01-25-2018, 09:34 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: Is there IF ANY function in Excel like in GBI???

    You're welcome.


    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
    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. Excel VBA Function Method API Windows Function User32.dll Alias Declare Library List . :)
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-17-2018, 07:51 PM
  2. Do Macros still perform a function even if that function is already set in an Excel file?
    By AKM1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-24-2014, 11:56 AM
  3. Excel 2010 VBA Sheets Copy function works, but then jumps to the beginning of a function
    By mycroft in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-08-2014, 05:46 AM
  4. Excel Match Function not working with Right function.
    By swap579579 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-16-2013, 09:00 PM
  5. Excel NORM.DIST function gives results greater than 1 with probability mass function. How
    By jano_rajmond in forum Excel General
    Replies: 1
    Last Post: 03-21-2012, 11:22 AM
  6. Excel 2007 Right function with Embeded Find Function help
    By Arazmus in forum Excel General
    Replies: 2
    Last Post: 03-30-2009, 04:04 AM
  7. [SOLVED] Excel - User Defined Function Error: This function takes no argume
    By BruceInCalgary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-18-2006, 04:05 PM

Tags for this Thread

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