Formula to do number and extract values

[bjnockle]

Looking for formula (non-array preferred) to look into column B2:B13, starting with B2=34. The formula will place B2=34 into E2 and then count B2=34 down until it gets to 1 (that is 34, 33, 32.......1). A second formula will extract Code values in column C into column F. The next row below 1 (E36) will be the new starting row for B3, which is 35. The formula will repeat this iteration until it gets to B13.

Note: the values in B2:B13 are manually entered and could change. Meaning I could decide to change B2=34 to B2=37.

Desired outcome in column E2:E402 and F2:402.

See sample file. Thanks

[JohnTopley]

in d2

0 (zero)

in d3

=SUM($B$2:B2)+1

Copy down

in F2

=INDEX($C$2:$C$13,MATCH(ROWS($1:1),$D$2:$D$14,1))

copy down


in E2 (Updated)

=INDEX($B$2:$B$13,MATCH($F2,$C$2:$C$13,0))-COUNTIF($F$2:$F2,$F2)+1

[JohnTopley]

Better ..See previous post

in E2

=INDEX($B$2:$B$13,MATCH($F2,$C$2:$C$13,0))-COUNTIF($F$2:$F2,$F2)+1

[kvsrinivasamurthy]

In E2

Please Login or Register  to view this content.

In F2

Please Login or Register  to view this content.

Both copied down till blank results are seen.

[bjnockle]

kvsrinivasamurthy: excellent solution. Thanks a lot.

[bjnockle]

JohnTopley: Outstanding solution. Thanks a lot.