Formula to identify next cell in column with different value

[pilot]

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?

[Mark W.]

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 ]

[pilot]

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.

[Mark W.]

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)?

[pilot]

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.

[Aladin Akyurek]

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

[Mark W.]

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 ]

[pilot]

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.

[Mark W.]

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 ]

[Aladin Akyurek]

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