Formula to determine max number in multiple dynamic arrays

[jberylec]

This one is proving tough for me! Hope the experts can help

I need to create a formula in F2 that can be copied down through hundreds of location/batch rows, on a new version of this spreadsheet each day. The formula needs to find the max number in D but only for each location/batch section and report only that one into F on the same row. I've manually typed them in F just for display purposes.

A and B rows are always merged for that entire location and batch, as shown. So I was trying to build a formula that would recognize each time a row in B isn't blank <>"" which indicates a new location/batch starts here, then include that row and all blank rows below it (they're merged so treated as blank cells) to find the MAX number in D and show that in F. Then of course it starts over IF B isnt blank again.

In case of a tie, show both.

FYI, there are a lot more columns of data, not relevant for this though because none can be used as an identifier, I also have many other formula columns that I paste into row 2 each morning, then drag them all down the entire sheet, so trying to keep this just a formula not VBA. Hoping to add this new column to my daily paste and drag routine. Ah. Exhausting just describing this. Ha! Thanks in advance

 

[Peter_SSs]

Thanks for trying, but I cannot really tell anything from a picture like that. Really need some sample data that I can actually copy to test with.
Seems like all the columns have changed significantly as well so it is possible that any adaptation to different columns/ranges could be a problem.

I will wait for your updated XL2BB sample data.

 

[jberylec]

Ok I was able to find and replicate the issue in which the original formula is counting blanks as the largest. Finally, here ya go! Sorry to jump back and forth, we'll tackle this one first, then the other opposite issue Thanks!

ALL012723-qw2jqz.xlsx
	A	B	C	D	E	F	G
1	LOC		VAL	VAL IF	LEN	CODE	IFNA
2	1	1	83	83	2	56	1
3		1	81	81	2	56	2
4		1	77	77	2	55	3
5		1	75.5	75.5	4	55
6		1	0		0	#VALUE!	1
7		1	0		0	#VALUE!	1
8		1	69	69	2	54
9	2	2	70	70	2	55	1
10		2	0		0	#VALUE!	1
11		2	66	66	2	54	2
12		2	45	45	2	52	3
13	3	3	54	54	2	53	2
14		3	0		0	#VALUE!	1
15		3	0		0	#VALUE!	1
16		3	18	18	2	49
17		3	95	95	2	57	1
18		3	42	42	2	52	3
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF(C2=0,"",C2)
E2:E18	E2	=LEN(D2)
F2:F18	F2	=CODE(D2)
G2:G18	G2	=IFNA(MATCH(COUNTIFS(B$2:B$20,B2,D$2:D$20,">"&D2)+1,{1,2,3},0),"")
B2:B18	B2	=B1+(A2<>"")

 

[Peter_SSs]

You have dropped off part of the formula
Post 15 and post 17
=IF(D2="","",IFNA(MATCH(COUNTIFS(E$2:E$1000,E2,D$2:D$1000,">"&D2)+1,{1,2,3},0),""))

Post 22
=IFNA(MATCH(COUNTIFS(B$2:B$20,B2,D$2:D$20,">"&D2)+1,{1,2,3},0),"")
You have adjusted the columns correctly, but it is missing the red part!

 

[jberylec]

Hi Peter, yes I purposely did that because it was hiding or causing something else I needed, cant remember right now. But I'll change all of them back and revisit. Thank you!

 

[Peter_SSs]

OK, let us know if any problems arise.

 

[jberylec]

Another quick one for ya I'm sure, but I need a formula for column E that will count how many rows in each unique array where column D is not blank (remember column B is already our unique helper column ). Thank you!

TESTING2.xlsx
	A	B	C	D	E
1	LOC		VAL	VAL IF	NEW EXPECTED VALUE
2	1	1	83	83	5
3		1	81	81	5
4		1	77	77	5
5		1	75.5	75.5	5
6		1	0		5
7		1	0		5
8		1	69	69	5
9	2	2	70	70	3
10		2	0		3
11		2	66	66	3
12		2	45	45	3
13	3	3	54	54	4
14		3	0		4
15		3	0		4
16		3	18	18	4
17		3	95	95	4
18		3	42	42	4
Sheet1

Cell Formulas
Range	Formula
B2:B18	B2	=B1+(A2<>"")
D2:D18	D2	=IF(C2=0,"",C2)

 

[Peter_SSs]

You would need to adjust the range in the formula but this would do it

=COUNTIF(B$2:B$20,B2)

Or this one would consume a little less resources

=IF(B2=B1,E1,COUNTIF(B$2:B$20,B2))

 

[jberylec]

You would need to adjust the range in the formula but this would do it

=COUNTIF(B$2:B$20,B2)

Or this one would consume a little less resources

=IF(B2=B1,E1,COUNTIF(B$2:B$20,B2))

The top option counts the rows, but unfortunately doen't check D to see if it's blank and exclude from the row count. I think we need multiple criteria and ranges in order to count only non-blanks in D which are in the same B range.

The bottom option seems to reference E but that was just my example of what this formula result should be, so we cant reference those cells. E is where this new formula will live

I tried fiddling around with stuff like the following but nah, COUNTIFS(B$2:B$1500,B2,D$2:D$1500,D2&"<>""")
Thanks in advance!

 

[Peter_SSs]

Sorry, I mis-read the earlier information.

I tried fiddling around with stuff like the following but nah, COUNTIFS(B$2:B$1500,B2,D$2:D$1500,D2&"<>""")

Close.

Try these (I would probably use the first one)

=IF(B2=B1,E1,COUNTIFS(B$2:B$1500,B2,D$2:D$1500,">0"))

=COUNTIFS(B$2:B$1500,B2,D$2:D$1500,">0")

 

[jberylec]

Option 2 worked out great! Thank you