Get the Max Value until criteria is met

X

xKidz

New Member
Joined
Apr 12, 2019
Messages
16
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
 
Last edited by a moderator:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
try this


Book1
ABCD
1First LinePO130PO4
2PO258
3PO349
4PO481
5PO553
6First LinePO615PO8
7PO726
8PO832
9PO924
10PO1010
11PO117
12First LinePO1271PO12
13PO1318
14PO1435
15PO1544
16PO1614
17First LinePO1744
Sheet2
Cell Formulas
RangeFormula
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)),"")
 
Upvote 0
AlanY said:
try this

ABCD
First LinePO1
PO2
PO3
PO4
PO5
First LinePO6
PO7
PO8
PO9
PO10
PO11
First LinePO12
PO13
PO14
PO15
PO16
First LinePO17

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]PO4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]58[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]49[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]81[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]53[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]PO8[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]

[TD="align: right"]26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"]32[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]71[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "]PO12[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]

[TD="align: right"]35[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]

[TD="align: right"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] "][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=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)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


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

Zywzcrt
 
Upvote 0
you're welcome

not quite sure about your follow on question, what after values you wanted to remove?
 
Upvote 0
it should work, check your formula again


Book1
ABCD
1First LinePO130PO4
2PO258
3PO349
4PO481
5PO553
6First LinePO615PO8
7PO726
8PO832
9PO932
10PO1032
11PO117
12First LinePO1271PO12
13PO1371
14PO1471
15PO1571
16PO1614
17First LinePO1744
Sheet1
Cell Formulas
RangeFormula
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)),"")
 
Upvote 0
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
 
Upvote 0
one way i can think of is to create a helper column that can be hidden like this


Book1
ABCDE
1First LinePO13030PO2
2PO25858
3PO34949
4Single DropPO4810
5PO55353
6First LinePO61515PO8
7PO72626
8PO83232
9PO93232
10PO103232
11PO1177
12First LinePO127171PO12
13PO137171
14PO147171
15PO157171
16PO161414
17First LinePO174444
Sheet1
Cell Formulas
RangeFormula
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)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Macro gives message box every time something is deleted
Replies
7
Views
250
Peter_SSs
Peter_SSs
R
  • Question Question
Raw Data Filter
Replies
1
Views
84
RobP
R
B
  • Question Question
Applying values to a database based on multiple criteria sets
Replies
1
Views
107
Baldred
B
O
  • Question Question
Count only one occurrence of all criteria being met
Replies
8
Views
2K
KRice
K
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

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