Get the Max Value until criteria is met

[xKidz]

I have a spreadsheet with over 2000 lines

I will start by explaining the above picture.
Columns AO and AP are there just for explanation purposes.
My expected results are in column AP.
Each value from the AN (Miles Column) are connected with a PostCode (Column AK).
I would like the formula to start when "First Line" in column G is met and stop when another "First Line" is met.
I would like the formula to take the max value from Column AN but just for the "First Line" to "First Line range" and then restart again.
Then I would like the formula to apply the PostCode to the lines where "Multi Drop" from column J shows up.
I have tried a few formulas with Match and Index but i just don't know how to restart the count whenever i hit "First Line" again.


EDIT:
Also posted here
https://www.excelforum.com/excel-fo...-value-until-criteria-is-met.html#post5112649

 

[AlanY]

try this

Book1
	A	B	C	D
1	First Line	PO1	30	PO4
2		PO2	58
3		PO3	49
4		PO4	81
5		PO5	53
6	First Line	PO6	15	PO8
7		PO7	26
8		PO8	32
9		PO9	24
10		PO10	10
11		PO11	7
12	First Line	PO12	71	PO12
13		PO13	18
14		PO14	35
15		PO15	44
16		PO16	14
17	First Line	PO17	44
Sheet2

Cell Formulas
Range	Formula
D1	=IFERROR(INDEX(OFFSET(A1,0,1,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),MATCH(MAX(OFFSET(C1,0,0,MATCH("First Line",OFFSET(A1,1,0,2000,1),0),1)),OFFSET(A1,0,2,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),0)),"")

 

[xKidz]

try this

	A	B	C	D
1	First Line	PO1	30	PO4
2		PO2	58
3		PO3	49
4		PO4	81
5		PO5	53
6	First Line	PO6	15	PO8
7		PO7	26
8		PO8	32
9		PO9	24
10		PO10	10
11		PO11	7
12	First Line	PO12	71	PO12
13		PO13	18
14		PO14	35
15		PO15	44
16		PO16	14
17	First Line	PO17	44

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula D1 =IFERROR(INDEX(OFFSET(A1,0,1,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),MATCH(MAX(OFFSET(C1,0,0,MATCH("First Line",OFFSET(A1,1,0,2000,1),0),1)),OFFSET(A1,0,2,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),0)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hi,

This is working as expected, thanks alot.
Any way that i can remove all the other values if First Line is not present ?

Looks like this for now

 

[AlanY]

you're welcome

not quite sure about your follow on question, what after values you wanted to remove?

 

[xKidz]

Seems my picture didn't get attached for some reason

https://imgur.com/a/O5qDIIY

It seems that it adds up more values around.
It does the job fine on the First Line though

 

[AlanY]

it should work, check your formula again

Book1
	A	B	C	D
1	First Line	PO1	30	PO4
2		PO2	58
3		PO3	49
4		PO4	81
5		PO5	53
6	First Line	PO6	15	PO8
7		PO7	26
8		PO8	32
9		PO9	32
10		PO10	32
11		PO11	7
12	First Line	PO12	71	PO12
13		PO13	71
14		PO14	71
15		PO15	71
16		PO16	14
17	First Line	PO17	44
Sheet1

Cell Formulas
Range	Formula
D1	=IFERROR(INDEX(OFFSET(A1,0,1,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),MATCH(MAX(OFFSET(C1,0,0,MATCH("First Line",OFFSET(A1,1,0,2000,1),0),1)),OFFSET(A1,0,2,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),0)),"")

 

[xKidz]

Hi,

I have added an IF NOT statement before the formula which takes care of the things.
I would like to ask something else though, i have added a new value in Column A "Single Drop", is there any way that your formula can ignore the post code of this line?
Also there will be the problem that the formula doesn't calculate on the last array of cells.

Your help is much appreciated

 

[AlanY]

one way i can think of is to create a helper column that can be hidden like this

Book1
	A	B	C	D	E
1	First Line	PO1	30	30	PO2
2		PO2	58	58
3		PO3	49	49
4	Single Drop	PO4	81	0
5		PO5	53	53
6	First Line	PO6	15	15	PO8
7		PO7	26	26
8		PO8	32	32
9		PO9	32	32
10		PO10	32	32
11		PO11	7	7
12	First Line	PO12	71	71	PO12
13		PO13	71	71
14		PO14	71	71
15		PO15	71	71
16		PO16	14	14
17	First Line	PO17	44	44
Sheet1

Cell Formulas
Range	Formula
D1	=IF(A1="Single Drop",0,C1)
E1	=IFERROR(INDEX(OFFSET(A1,0,1,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),MATCH(MAX(OFFSET(D1,0,0,MATCH("First Line",OFFSET(A1,1,0,2000,1),0),1)),OFFSET(A1,0,3,MATCH(A1,OFFSET(A1,1,0,2000,1),0),1),0)),"")

 

[xKidz]

This works wonders,

Thank you sir!

 

[AlanY]

glad that help, you're welcome