Formula to identify next cell in column with different value
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Formula to identify next cell in column with different value

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Given a column with many repetitive values, how would you write a formula to identify the next cell that differs from the one above it? For example, B2:B6 contain the following values: 50, 50, 50, 75, 75. If a VLOOKUP points you to B3, how would you then reference B5 as the next different cell in the column?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What do you mean by "points you to B3"?

    Please understand that a VLOOKUP returns a value -- not a cell reference. Furthermore, =VLOOKUP(50,$B$2:$B$6,1), would cease its search after encountering 50 in cell B2.

    [ This Message was edited by: Mark W. on 2002-03-19 10:36 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Intent is to match a value in Col A, then reference the corresponding value in Col B (B3 in example) in one place and reference next different cell below B3 (B5 in example) in another.

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 11:34, pilot wrote:
    Intent is to match a value in Col A, then reference the corresponding value in Col B (B3 in example) in one place and reference next different cell below B3 (B5 in example) in another.
    But, do you need a reference to B5 or just the value in B5 (75, in this case)?

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just need the value 75. Application is sick/vacation earnings schedule. Col A = years (1 - xx), Col B = number of days (10 for several years, then 15 for several years, then 20, etc.) Once I determine how many years the employee has worked, Col B shows earned vacation days this year. Next incremented value in Col B shows how much earned when it next increases and cell A of that row shows how many years he will have worked by then and can be used with hire date to calculate the date that will occur.

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-19 09:41, pilot wrote:
    Given a column with many repetitive values, how would you write a formula to identify the next cell that differs from the one above it? For example, B2:B6 contain the following values: 50, 50, 50, 75, 75. If a VLOOKUP points you to B3, how would you then reference B5 as the next different cell in the column?
    Given the sample

    {50;50;50;75;75}

    in A2:A6,

    =OFFSET(A2,MATCH(50,A2:A6)+1,0)

    will give you the first value, which is different from 50 (your lookup value), that is, 75.

    Is this what you are looking for?

    Aladin

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 13:30, Aladin Akyurek wrote:

    Given the sample

    {50;50;50;75;75}

    in A2:A6,

    =OFFSET(A2,MATCH(50,A2:A6)+1,0)

    will give you the first value, which is different from 50 (your lookup value), that is, 75.

    Is this what you are looking for?

    Aladin
    Make that...

    =OFFSET(A2,MATCH(50,$A$2:$A$6),)

    ...because =OFFSET(A2,MATCH(50,A2:A6)+1,0) will fail with the following data set...

    {50;50;50;75;80}


    [ This Message was edited by: Mark W. on 2002-03-19 13:41 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Very close but if I didn't know what the values in Col B were, I wouldn't know 50 is the value associated with the row that would be selected by matching number of years worked with Col A (see my prev post), so the "50" in your formula would have to be identified relatively, like an OFFSET from selected Col A cell.

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Perhaps this is what you want...

    {=OFFSET($A$2,SUM((OFFSET(A2,,,COUNT($A$2:$A$6)-1)=OFFSET(A2,1,,COUNT($A$2:$A$6)-1))+0)+1,)}

    Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.


    [ This Message was edited by: Mark W. on 2002-03-19 14:03 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,807
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-19 13:39, pilot wrote:
    Very close but if I didn't know what the values in Col B were, I wouldn't know 50 is the value associated with the row that would be selected by matching number of years worked with Col A (see my prev post), so the "50" in your formula would have to be identified relatively, like an OFFSET from selected Col A cell.
    Care to construct a small sample or extract a small sample of your own data?

    If uou inclined to do so, select an empty cell, type =, select the sample including the labels if any, hit F9, copy what you see, and paste it in the follow-up.

    Aladin


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com