Formula to identify next cell in column with different value

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
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?
 
Must leave on travel, will probably be a few days before I can try your formula and respond. Thanks, appreciate all the efforts.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Aladin, here's a small sample

{"YEARS","SICK","VACATION";
1,116.25,77.5;2,155,77.5;
3,155,77.5;4,232.5,77.5;
5,232.5,116.25;6,232.5,116.25;
7,310,116.25;8,310,116.25;
9,310,116.25;10,465,116.25;
11,465,116.25;12,465,116.25;
13,465,116.25;14,465,116.25;
15,620,155;16,620,155}
This message was edited by Juan Pablo G. on 2002-03-19 19:37
 
Upvote 0
Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.
This message was edited by pilot on 2002-03-19 19:17
 
Upvote 0
On 2002-03-19 19:15, pilot wrote:
Meant to include a bit more explanation. Will write formula to determine which yearly anniversary falls in this year. Assume 4th. Sample data shows 232.5 sick and 77.5 vacation earned in current year. Those values get plugged in cells on employee's worksheet. Also want to show when next higher level of sick and vacation will happen. Sample data shows year 7 for sick and year 5 for vacation. Employee worksheet will then show HireDate + 7 years is next sick increment and hours goes to 310; HireDate + 5 years is next vacation increment and hours goes to 116.25.
This message was edited by pilot on 2002-03-19 19:17

Pilot,

Thanks providing the sample data & explanation.

I'll assume that A1:C17 in sheet x houses the data you provided, including labels. I'll further assume that you have a sheet called Employee, which has 4 as value (your example) in A2 (either computed or inputted).

In B2 enter:

=OFFSET(x!$B$2,MATCH(A2,x!$A$2:$A$17),0)

In C2 enter:

=OFFSET(x!$C$2,MATCH(A2,x!$A$2:$A$17,0)-1,0)

In A3 enter:

=INDEX(x!$A$2:$A$17,MATCH(B3,x!$B$2:$B$17,0))

In B3 enter:

=OFFSET(x!$B$2,MATCH(B2,x!$B$2:$B$17),0)

In C3 enter:

=OFFSET(x!$C$2,MATCH(B2,x!$B$2:$B$17),0)

[ If the formula does not work the way you want it, try

=OFFSET(x!$C$2,MATCH(A3,x!$A$2:$A$17),0)

instead. Both formulas return the same value, but pick out that value differently. ]

A3:C3 should be downwards draggable/copiable.

Alternatively, in x, name the range A2:A17 Years via the Name box, the range B2:B17 SickLevel, and the range C2:C17 Vacation. Then,

in B2 enter:

=INDEX(SickLevel,MATCH(A2,Years))

in C2 enter:

=INDEX(Vacation,MATCH(A2,Years),0)

in A3 enter:

=INDEX(Years,MATCH(B3,SickLevel,0))

in B3 enter:

=INDEX(SickLevel,MATCH(B2,SickLevel)+1)

in C3 enter:

=INDEX(Vacation,MATCH(B2,SickLevel),0)

[ If the formula does not work the way you want it, try

=INDEX(Vacation,MATCH(A3,Years),0)

instead. Both formulas return the same value, but pick out that value differently. ]

A3:C3, like as above, should be downwards draggable/copiable.

That is what I get in the processing area in Employee:

{4,232.5,77.5;
7,310,116.25}

Hope this is what you're looking for.

Aladin
This message was edited by Aladin Akyurek on 2002-03-20 14:56
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top