+ Reply to Thread
Results 1 to 6 of 6

Distinguish whether the next cell down is <20 OR a word in a different column is present..

  1. #1
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Distinguish whether the next cell down is <20 OR a word in a different column is present..

    Hello,

    In column A I have employee ID numbers.
    In column B i have an event list.

    I would like column C to distinguish when...
    ...the word "Create" is in column B...
    ...whilst the number beside it is less than 20 in column A...
    ...whilst EITHER of the following two incidents occur:
    1) the next cell down in column A is also less than 20 (it can be the same number as the cell above)
    OR
    2) The next word down in column B is either "Work", "People" or "Team".

    so in the following example,
    2 Create [POSTIVE RESULT]
    6 Apple
    will give a positive outcome.

    also:
    2 Create [POSITIVE RESULT]
    35 People
    will give a positibe outcome because of the "people" word

    however,
    2 Create
    35 Apple
    will not give a result


    Thank you
    Last edited by tlacloche; 09-25-2013 at 01:06 PM.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

    Try

    =IF(AND(B2="Create",A2<20,OR(A3<20,ISNUMBER(MATCH(B3,{"People","Team","Work"},0)))),"Positive","")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

    =IF(AND(B2="Create",A2<20,OR(B3={"People","Work","Team"},A3<20)),"Positive Result","")

  4. #4
    Forum Contributor
    Join Date
    03-20-2013
    Location
    London, England
    MS-Off Ver
    Excel 2011
    Posts
    474

    Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

    thanks to both of you.

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

    Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese

    Nice daffodil,

    I still forget on occasion that, used in the correct context, a simple OR condition will pass an array as effectively, and more simply, than other constructions.

    Regards
    Last edited by XOR LX; 09-25-2013 at 01:34 PM.
    Click * below if this answer helped

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

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Distinguish whether the next cell down is <20 OR a word in a different column is prese



    Thanks!

+ 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] distinguish whether a cell value is followed by another value (but with many conditions)..
    By tlacloche in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-24-2013, 09:50 PM
  2. [SOLVED] Distinguish whether a cell is followed (downwards) by a value (with a condition).
    By tlacloche in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-21-2013, 08:36 AM
  3. Macro to distinguish sequential numbers and report a value to a different cell
    By capnhud in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-10-2011, 08:42 AM
  4. Replies: 4
    Last Post: 03-18-2011, 06:05 PM
  5. Copy For Cell Values Present and not Formulas Present
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2009, 12:10 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