Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Feb 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =OR( (Excel 2003)

    Is there a way to condense down an OR statement when you are clicking on individual cells across a long sheet to see if it equal a particular text phrase and if it matches it's true otherwise it's false? Since it could equal text in other cells the individual cells have to be selected so a range canít be used.

    Right now it's a lengthy OR statement. =OR(AY = "XXXXXX", BK = "XXXXXX", BY = "XXXXXX", CC = "XXXXXX", etc...)

    There has got to be an easier way. I tried clicking on each cell and then naming it as a group but it didn't like it.

    I am not the most savy user of this program as you can probably tell. Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =OR( (Excel 2003)

    Welcome to Woody's Lounge!

    I don't think there is a really easy solution for this. If it was a contiguous range, you could use an array formula (confirmed with Ctrl+Shift+Enter) such as =OR(A1:A10="test") but that doesn't work for non-contiguous cells.

    Wouldn't it be possible to use a contiguous range? For example, if you want to look at cells A1, A4, A7 and A10 while the cells in between are blank, you might as well look at A1:A10.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: =OR( (Excel 2003)

    If it is contiguous I would think something like:
    =ISNUMBER(MATCH("test",A1:A10,0))

    Would be more efficient than an Array...

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: =OR( (Excel 2003)

    You could use an array if there is a pattern to your columns. Is there a pattern to your non-contiguous columns?

    You could also create an intermediate row with the blanks between them and then use Match on this range...

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =OR( (Excel 2003)

    Good thinking - you're most probably correct. Thanks!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: =OR( (Excel 2003)

    I am going to throw in my tu' pence worth in here and have developed this to allow you to create it with a named noncontiguous range like so

    I placed my values in A1, A3 and A5. I named the range by highlighting these cells and called it NCont

    I then typed the following

    =OR(RANK(1,NCont)=1)

    It works more like an AND function but I thought I would add it here as a beginning of a discussion as the RANK function allows noncontiguous ranges in its syntax
    Jerry

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: =OR( (Excel 2003)

    or <code>=COUNTIF(A1:A10,"test")>0</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: =OR( (Excel 2003)

    I don't know if it's much better, but you could use:
    <code>=SUM(COUNTIF(INDIRECT({"AY1","BK1","BY1","CC 1"}),"XXXXXX"))>0</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: =OR( (Excel 2003)

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Yes that is also good...

    But I am unsure of the efficiency of the 2. In can make a difference in a large range, countif will have to go through each and every cell in the range even after finding a match. The Match routine should quit when the first is found...

    Steve

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: =OR( (Excel 2003)

    Very true although it shouldn't be much of an issue unless there are large ranges and the formula is repeated a lot. Besides, I was about to throw INDIRECT with an array into the mix, so efficiency is out the window anyway! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: =OR( (Excel 2003)

    Efficiency is never really an issue unless there are large ranges and the formula is repeated a lot. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>But with the larger spreadsheets available in XL2007 and each array formula potentially having thousands if not hundreads of thousands calculations, it can become an issue.

    In my experience, even though built-in formulas in general are much more efficient than code, at times code can do things faster than formulas (especially array formulas, which are inefficient and duplicate calculation effort...)

    My feeling comes down to go with what you are comfortable with if it works. Worry about the efficiency when it starts not working (ie gets too sluggish)...

    Steve

  12. #12
    New Lounger
    Join Date
    Feb 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =OR( (Excel 2003)

    Thank you all for your suggestions but is one of these going to actually work? If you had to pick one which one should be tried?

    Thanks.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =OR( (Excel 2003)

    For a group of discontiguous cells, Rory's suggestion in <post:=698,511>post 698,511</post:> is probably the best one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •