Anyone Could help me with Arrayformula to find max value for this type of ada. In a row there are series of number, always start with 0 (Zero) and followed by few numbers, then start again with 0 (Zero) and so on
What is the formula to find max value for uncertain row of numbers Data 1 is 3 row, data 2 is 2 row, data 3 is 3 row data 4 is 6 row and so on its total random
Example Link
CodePudding user response：
Answer:
As an alternative to using formulae, you can do this with an Apps Script 
I hope this is helpful to you!
CodePudding user response：
I'll put this one out there as well:
=ArrayFormula(if(A2:A<>"",sort(B2:B,vlookup(row(A2:A),if(A2:A<>"",row(A2:A)),1,true),1,B2:B,0),))
What I'm trying to do is sort first on the group number (first row number of each group) obtained from the vlookup then on the value in B2.
CodePudding user response：
I've added a sheet ("Erik Help"). Per my last comment to your original post (comment #3 overall), your data setup is not clear. So I have added two formula versions along with a note.
Formula V1 (in C2):
=ArrayFormula(IF(A2:A="",,VALUE(REGEXEXTRACT(VLOOKUP(A2:A&"*",SORT(FILTER(VLOOKUP(ROW(B2:B),FILTER({ROW(B2:B),A2:A},A2:A<>""),2,TRUE)&"~"&TEXT(B2:B,"000"),B2:B<>""),1,0),1,FALSE),"~(. )$"))))
Formula V2 (in D2):
=ArrayFormula(IF((B2:B="") (B2:B>0),,VALUE(REGEXEXTRACT(VLOOKUP(TEXT(ROW(B2:B),"000000")&"*",SORT(FILTER(TEXT(VLOOKUP(ROW(B2:B),FILTER(ROW(B2:B),ISNUMBER(B2:B),B2:B=0),1,TRUE),"000000")&"~"&TEXT(B2:B,"000"),B2:B<>""),1,0),1,FALSE),"~(. )$"))))
My on-sheet note:
Use the V1 formula in C2 if your real-world sheet will have merged data cells, since it is a bit simpler to digest. (Currently, it will only produce results through Row 27, because that is as far as you have entered data in Col A.)
Use the V2 formula in D2 if your real-world sheet will not have data in Col A, and if results must rely solely on Col-B data. (This formula will work whether you have Col-A data or not; it's just longer than necessary, if you will have Col-A data.)
ADDEDNDUM: I recommend the solution posted by Tom Sharpe. It's the most straightforward approach. I had forgotten that pure numbers would sort in correct order without the conversions I used above. (Numbers in other situations sort 1,11,100,2,27, etc.) Being that they will sort in order without additional "fiddling," Tom's solution is the way to go.