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]

Welcome to the MrExcel board!

For the future, it would help if you can provide your sample data and expected results in a form that helpers can copy to test with. Have a look at XL2BB
Give this a try. Where I have used 1000 near the end of the formula, make that a number that will easily exceed the number of rows of data you ever likely expect.

23 01 15.xlsm
	A			D	E
1	Location			Max
2	1			86
3				87	87
4				84
5				82
6	2			77
7				71
8				73
9				78
10				74
11				81	81
12	3			97
13				0
14				98	98
15				90
16				95
17	4			62
18				64
19				61
20				66
21				70	70
22				69
23				67
24				70	70
Max per group

Cell Formulas
Range	Formula
E2:E24	E2	=IF(D2=MAX(INDEX(D:D,MATCH(COUNT(A$2:A2),A:A,0)):INDEX(D:D,IFNA(MATCH(COUNT(A$2:A2)+1,A:A,0)-1,1000))),D2,"")

 

[Peter_SSs]

Another option, keeping the formulas a bit simpler but using a helper column (which could be hidden)

23 01 15.xlsm
	A			D	E	F
1	Location			Max
2	1			86		1
3				87	87	1
4				84		1
5				82		1
6	2			77		2
7				71		2
8				73		2
9				78		2
10				74		2
11				81	81	2
12	3			97		3
13				0		3
14				98	98	3
15				90		3
16				95		3
17	4			62		4
18				64		4
19				61		4
20				66		4
21				70	70	4
22				69		4
23				67		4
24				70	70	4
Max per group (2)

Cell Formulas
Range	Formula
E2:E24	E2	=IF(D2=MAXIFS(D$2:D$1000,F$2:F$1000,F2),D2,"")
F2:F24	F2	=IF(A2="",F1,A2)

 

[jberylec]

Hi Peter! I think I've got the XL2BB working, going to try it below to show you the results. Thank you! FYI, the first formula you posted seems to stop working at line 60, not showing a result for that group, then any further groups down it returns the n/a "Value not available" error. Weird. I cant find anything in the formula that would trip that up, nor anything in the data. Hmm. However, your second post doesn't seem to have a problem like this, You'll see that one in F and G so I'm good with this one! You're a life saver my friend. Oh, and by the way do you know how to get rid of the formula popup that blocks visibility to cells behind it when editing a formula? I use the edit bar at the top, so I dont need the popup covering my data, very annoying and I cant find anywhere to turn it off. Screen shot attached. Thanks again!

TESTING.xlsx
	A	B	C	D	E	F	G
60	9			87		9
61				93		9
62				91		9
63				86		9
64				89		9
65				88		9
66				98		9	98
67				87		9
68				92		9
69				91		9
70				93		9
71	1			0	#N/A	1
72				0	#N/A	1
73				0	#N/A	1
74				82	#N/A	1
75				83	#N/A	1
76				86	#N/A	1
77				87	#N/A	1	87
78				72	#N/A	1
Table 1

Cell Formulas
Range	Formula
E60:E78	E60	=IF(D60=MAX(INDEX(D:D,MATCH(COUNT(A$2:A60),A:A,0)):INDEX(D:D,IFNA(MATCH(COUNT(A$2:A60)+1,A:A,0)-1,205))),D60,"")
F60:F78	F60	=IF(A60="",F59,A60)
G60:G78	G60	=IF(D60=MAXIFS(D$2:D$205,F$2:F$205,F60),D60,"")

 

[jberylec]

Ut oh, I spoke too soon, there are spots where option 2 doesn't return a max either. I think its maybe because the formulas are looking at the whole range of D:D for the criteria and matching based on the value of A (the location number)? But the problem with that is there will be many other groups of rows throughout the sheet with that location number further down. So if it's including those too and giving the max result to the overall max even if its way down on row 800 in a separate group? Just thinking out loud here, haven't confirmed that. But when I copy down the formulas in a live sheet with 1000s of rows and then scroll through, there are a lot of location groups without a max reported in either of these new columns. And in addition the n/a errors start in the first formula column.

In the example above we need the formula to only look at rows 60-70, and not include any other merged location groups further down that have the same location number. My thought was to have it recognize each time there's a number in A start the criteria range there and include all rows until it sees a number in A again, which ends the criteria range one row above, and of course starts a new criteria range to check for the max from that group. And so on..

I could be wrong on all that though, it's late and I've been looking at numbers all evening. Ha! Thanks again for all the help!

 

[Peter_SSs]

Well, they do not work because your sample data for Location went 1 then 2 then 3 then 4. A fairly reasonable assumption for helpers that know nothing else about your circumstances is that that pattern would continue.
Sample data that is truly representative is more likely to elicit robust responses.

See if this is better

23 01 15.xlsm
	A			D	E
1	Location			Max
2	1			86
3				87	87
4				84
5				82
6	2			77
7				71
8				73
9				78
10				74
11				81	81
12	1			97
13				0
14				98	98
15				90
16				95
17	2			62
18				64
19				61
20				66
21				70	70
22				69
23				67
24				70	70
Max per group

Cell Formulas
Range	Formula
E2:E24	E2	=IF(D2=MAX(INDEX(D:D,AGGREGATE(14,6,ROW(A$2:A2)/(A$2:A2<>""),1)):INDEX(D:D,IFERROR(AGGREGATE(15,6,ROW(A3:A$1000)/(A3:A$100<>""),1)-1,1000))),D2,"")

 

[jberylec]

Ah ha, yes I thought of that consecutive number thing after the fact. Sorry I didn't capture enough of the sheet to show that the numbers start over and over. Anyhow this new one seems to have done the trick! Awesome. You rock! Any idea on disabling that formula popup?

 

[jberylec]

Peter, if possible, I'd like to add another column that looks at the same stuff but instead of returning the max value or blank, it returns 1, 2, or 3 (which would be the max, the second highest, and the third highest. This would be incredibly useful Thanks again sir! This site needs a tipping option so we can give back to you folks who help us solve these difficult problems!

 

[Peter_SSs]

Any idea on disabling that formula popup?

I don't know what you mean.

I'd like to add another column that looks at the same stuff but instead of returning the max value or blank, it returns 1, 2, or 3 (which would be the max, the second highest, and the third highest.

I am changing back to the idea of an extra helper column as it could be used for the previous question and this new one (& possibly more).
For the new one I am not sure whether you wanted the results as shown in col G or those in col G (or something else again)?

jberylec.xlsm
	A			D	E	F	G	H
1	Location			Max		Max	Top3	Top3
2	1			86	1		86	2
3				87	1	87	87	1
4				84	1		84	3
5				82	1
6	2			77	2		77	3
7				71	2
8				73	2
9				78	2		78	2
10				74	2
11				81	2	81	81	1
12	1			97	3		97	2
13				0	3
14				98	3	98	98	1
15				90	3
16				95	3		95	3
17	4			62	4
18				64	4
19				61	4
20				66	4
21				70	4	70	70	1
22				69	4		69	3
23				67	4
24				70	4	70	70	1
Max per group (3)

Cell Formulas
Range	Formula
E2:E24	E2	=E1+(A2<>"")
F2:F24	F2	=IF(D2=MAXIFS(D$2:D$1000,E$2:E$1000,E2),D2,"")
G2:G24	G2	=IF(D2>=AGGREGATE(14,6,D$2:D$1000/(E$2:E$1000=E2),3),D2,"")
H2:H24	H2	=IFNA(MATCH(COUNTIFS(E$2:E$1000,E2,D$2:D$1000,">"&D2)+1,{1,2,3},0),"")

This site needs a tipping option so we can give back to you folks who help us solve these difficult problems!

That would be a breach of #5 of the Forum Rules
We do it because we want to, not for any financial reward.

 

[jberylec]

Ah, the formula popup I talked about in post #4 and included a screenshot. Thanks again! I'll give these latest versions a try. Awesome