Removing the need of a helper column

[Marvo]

Good morning, yesterday I posed a question regarding the attached image and Stephen Crump kindly gave me the perfect solution.

=COUNTIFS(A2:A34,"League",B2:B34,"H",D2:D34,E14)

What has got me thinking is the solution doesn't reference column C at all and as column D is a helper column that refers to Column C, is it possible to "cut out the middle man" completely, meaning I could dispense with the helper column? It would be a great boon if so as the several helper columns (60 in all) really slow the workbook down.

The formula in D3 is =IF(($A3="League")*($B3="H"),IF($C3="W",SUM(1,D2),0),N(D2))

Copied down

Many thanks for any help offered and if it can't be done, so be it. Just wondered.

 

[KRice]

Okay, that helps. So column A’s use by the formula is straightforward. For any given row, if the corresponding (same row) value in A is “all”, then use all (i.e., don’t apply a filter to the data for the match type criterion), otherwise apply the filter for “league”.

 

[Marvo]

Yes. I think its easier than you think.

It could be set up like this, All Matches together and then just League.

Cobblers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ALL-TIME RECORDS
2	ALL MATCHES		W	Wins		D	Draws		L	Defeats		GF			GA
3			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
4	Consecutive	ALL	11	1	28/12/1899	6	2	1983 & 2011	7	4	1972, 1985, 1988 & 1992	28	1	03/10/1908	29	1	28/12/1965
5		H	14	1	16/04/1927	5	1	21/03/1981	6	2	1985 & 2002	54	1	28/01/0928	16	1	13/10/1900
6		A	6	2	2004 & 2015	4	2	1913 & 1994	16	1	27/10/1906	19	1	27/03/1959	40	1	01/09/1926
7			NO L			NO D			NO W			NO GA			NO GF
8	Longest Run	ALL	27	1	23/01/2016	29	1	05/02/1927	19	2	1969 & 1993	6	2	1910 & 1930	7	1	07/04/1939
9		Home	33	1	22/02/1913	28	1	31/08/1957	11	1	17/01/1990	9	1	25/08/1923	6	1	14/12/2002
10		Away	14	1	13/02/2016	21	1	12/04/1908	28	1	27/01/1906	6	1	12/04/1997	7	2	1938 & 1995
11	League		Wins			Draws			Defeats			GF			GA
12	Only		TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
13		ALL	11	1	28/12/1899	6	2	1983 & 2011	8	1	26/10/1935	28	1	29/08/2015	27	1	28/09/1965
14	Consecutive	Home	12	2	1899 & 2027	5	1	21/03/1981	5	5	1905, 1985, 1999, 2002 & 2016	75	1	28/12/1926	19	1	13/10/1900
15		Away	8	1	21/11/2015	5	1	06/09/1994	15	1	27/10/1906	18	1	27/03/1959	42	1	20/02/1965
16			NO L			NO D			NO W			NO GA			NO GF
17		ALL	31	1	28/12/2015	29	1	05/02/1927	18	2	1969 & 2011	6	2	1930 & 1975	7	1	07/04/1939
18	Longest Run	Home	33	1	22/02/1913	26	2	1898 & 1984	11	1	17/01/1990	9	1	25/08/1923	5	1	14/12/2002
19		Way	19	1	31/10/2015	23	1	30/04/1966	33	1	01/01/1921	6	1	12/04/1997	8	1	28/03/1967
Records

Cell Formulas
Range	Formula
C4	C4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=$C$2,s),IF(t<>$C$2,s))))
C5	C5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C6	C6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
F4	F4	=LET(t,ALL!$K:$K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F5	F5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
F6	F6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
I4	I4	=LET(t,ALL!K:K,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
I5	I5	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B5),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I6	I6	=LET(t,FILTER(ALL!$K:$K,ALL!$E:$E=B6),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
L4	L4	=LET(t,ALL!L:L,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
L5	L5	=LET(t,FILTER(ALL!L:L,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L6	L6	=LET(t,FILTER(ALL!L:L,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O4	O4	=LET(t,ALL!$M:$M,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
O5	O5	=LET(t,FILTER(ALL!M:M,ALL!$E:$E="H"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O6	O6	=LET(t,FILTER(ALL!M:M,ALL!E:E="A"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
C8	C8	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C9	C9	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C10	C10	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
F8	F8	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F9	F9	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F10	F10	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
I8	I8	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I9	I9	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I10	I10	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
L8	L8	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L9	L9	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L10	L10	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O8	O8	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O9	O9	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O10	O10	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
C13	C13	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C14	C14	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>"W",s))))
C15	C15	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A11)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>C2,s))))
F13	F13	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J=A11),MAX(FREQUENCY(IF(t=F2,SEQUENCE(ROWS(t))),IF(t<>F2,SEQUENCE(ROWS(t))))))
F14	F14	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F15	F15	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="D",s),IF(t<>"D",s))))
I13	I13	=LET(t,FILTER(ALL!$K:$K,ALL!$J:$J=A11),MAX(FREQUENCY(IF(t=I2,SEQUENCE(ROWS(t))),IF(t<>I2,SEQUENCE(ROWS(t))))))
I14	I14	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
I15	I15	=LET(t,FILTER(ALL!$K:$K,(ALL!$J:$J=A11)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
L13	L13	=LET(t,FILTER(ALL!$L:$L,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L14	L14	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L15	L15	=LET(t,FILTER(ALL!$L:$L,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O13	O13	=LET(t,FILTER(ALL!$M:$M,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O14	O14	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="H")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O15	O15	=LET(t,FILTER(ALL!$M:$M,(ALL!$J:$J="League")*(ALL!$E:$E="A")),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
C17	C17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C18	C18	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C19	C19	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
F17	F17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F18	F18	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F19	F19	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
I17	I17	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I18	I18	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I19	I19	=LET(t,FILTER(ALL!$K:$K,(ALL!$K:$K<>"")*(ALL!$J:$J=A11)*(ALL!$E:$E=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
L17	L17	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L18	L18	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L19	L19	=LET(t,FILTER(ALL!$M:$M,(ALL!$M:$M<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O17	O17	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O18	O18	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="H")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O19	O19	=LET(t,FILTER(ALL!$L:$L,(ALL!$L:$L<>"")*(ALL!$J:$J="League")*(ALL!$E:$E="A")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))

 

[KRice]

The previous layout would be easier to implement because the “only W’s” versus “no L’s” versions of the formula are different. So keeping Consecutive and Longest Runs in separate groupings requires less switching between formula types. The only issue I see is the streak dates are not easily determined using the current formulas.

 

[Marvo]

I'll put it back to the former.

In the search box you can look for as many consecutive or longest runs you like. So logic says there must be a way there because it works.

I have an old copy somewhere which had working VBA but somewhere along the line cells/rows/columns got moved and it stopped working. Do you want me to have a look for it, maybe it could be edited to do the job now. It's all gobblegook to me.

 

[KRice]

I discovered one issue with the formula you've been using. When the calculation type is a "NO" type (e.g., Longest Run with NO Draws), and the maximum run sought involves the first n items in the "outcome" column (whether that is the W/L/D results column or a GF or GA column), the formula will report a run of n+1. The reason is the formula uses a full column reference, so the column headers are included in the comparison logic. And since none of the headers are the same as the value that terminates the run (i.e., the results column headings are not W, L, D, 0, or some other number), the formula will consider that heading as part of the run. Typically this won't produce an incorrect run length because the longest run is found somewhere else in the list, far away for the first row. But to make the formula(s) more robust, something like this avoids the problem altogether:

=LET(t,FILTER($K2:$K10000,($K2:$K10000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>"D",s),IF(t="D",s))))

An added benefit is that the full column reference is avoided, which should help with performance.

If you are interested in a single formula (albeit many parts) solution, the following formula determines where the upper and lower parts of the summary table are based on where the words "Consecutives" and "Longest Runs" are found. And then based on those row indexes, the outcomes involved in determining streaks reported by different sections of the table are determined from the first column subheaders where we find "W", "D", "L", "GF", "GA"...and all of the "NO" versions. So, other than adjusting the first few variables to adapt the formula to your worksheet(s), no further editing should be needed. But I don't know how this might perform on a much larger data set. The formula delivers max streak length, number of times, and dates for the streaks for upper and lower sections without any helper cells. The formula is copied into only the first cells in each sub-block column (e.g., C22:C27 and C30:C35, then corresponding cells in column F, and so on across the table). To make this formula work with the GF and GA columns, inside the formula, numeric scores for GF and GA are converted into letter codes, either "N" for nil, or "S" for something other than 0. In this way, the same algorithm can be used for determining streak lengths.

ALL-TIME RECORDS
CONSECUTIVES		W	Wins		D	Draws		L	Defeats		GF	Goals For		GA	Goals Against
		TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
ALL	ALL	7	1	10/03/2024	2	1	14/02/2024	2	2	2024, 03/03/2024	22	1	27/02/2024	15	1	03/03/2024
	H	5	2	2024, 29/02/2024	1	1	13/02/2024	2	1	03/02/2024	13	1	29/02/2024	7	1	02/03/2024
	A	6	1	10/03/2024	1	3	2024, 2024, 27/02/2024	3	1	11/02/2024	11	1	27/02/2024	9	1	03/03/2024
League	ALL	5	1	26/02/2024	2	1	14/02/2024	2	1	03/02/2024	20	1	27/02/2024	14	1	03/03/2024
	H	6	1	04/03/2024	1	1	13/02/2024	2	1	03/02/2024	13	1	04/03/2024	6	1	29/02/2024
	A	3	1	26/02/2024	1	3	2024, 2024, 27/02/2024	2	1	07/02/2024	10	1	27/02/2024	9	1	03/03/2024
LONGEST RUNS		NO L			NO D			NO W			NO GA			NO GF
		TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
ALL	ALL	9	1	27/02/2024	12	2	2024, 10/03/2024	2	4	2024, 2024, 2024, 03/03/2024	2	1	05/03/2024	1	4	2024, 2024, 2024, 02/03/2024
	H	7	1	15/02/2024	9	1	04/03/2024	2	1	03/02/2024	1	3	2024, 2024, 04/03/2024	1	2	2024, 02/03/2024
	A	6	1	10/03/2024	9	1	10/03/2024	3	1	11/02/2024	1	2	2024, 05/03/2024	1	2	2024, 28/02/2024
League	ALL	9	1	27/02/2024	10	1	12/02/2024	2	3	2024, 2024, 28/02/2024	1	4	2024, 2024, 2024, 04/03/2024	1	3	2024, 2024, 28/02/2024
	H	6	2	2024, 04/03/2024	8	1	04/03/2024	2	1	03/02/2024	1	3	2024, 2024, 04/03/2024	1	1	03/02/2024
	A	5	1	27/02/2024	4	1	12/02/2024	2	3	2024, 2024, 28/02/2024	1	1	17/02/2024	1	2	2024, 28/02/2024

=LET(loc, $B22, src, All!$B$2:$M$10000, t, $A$20:$Q$35, a, INDEX(t, , 1),
idxCon, MATCH("Consecutives", a, 0), idxLR, MATCH("Longest Runs", a, 0), curridx, ROW() - ROW(INDEX(a, 1)) + 1,
dyna, INDEX(a, SEQUENCE(curridx)),
mt, INDEX(dyna, XMATCH(TRUE, dyna <> "", 0, -1)),
ct, IF(curridx >= idxLR, "Longest Runs", "Consecutives"),
oc_input, IF(curridx >= idxLR, INDEX(t, idxLR, COLUMN()), INDEX(t, idxCon, COLUMN())),
oc, XLOOKUP(oc_input, {"W","NO L","D","NO D","L","NO W","GF","NO GA","GA","NO GF"}, {"W","L","D","D","L","W","S","S","S","S"}),
sect, IFS(OR(oc_input = {"W","D","L","NO L","NO D","NO W"}), 1, OR(oc_input = {"GF","NO GF"}), 2, OR(oc_input = {"GA","NO GA"}), 3),
fsrc_cols, CHOOSE(sect, {1,4,9,10}, {1,4,9,11}, {1,4,9,12}),
fsrc, FILTER(src, INDEX(src, , 10) <> ""),
data, INDEX(fsrc, SEQUENCE(ROWS(fsrc)), fsrc_cols),
allr_data, SEQUENCE(ROWS(data), , 1, 0),
loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc),
mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt),
fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1,4}), loc_ary * mt_ary),
allr_fd, SEQUENCE(ROWS(fd)),
oc_col, IFS(sect = 1, INDEX(fd, , 2), OR(sect = 2, sect = 3), IF(INDEX(fd, , 2) > 0, "S", "N")),
revfd, IF(SWITCH(ct, "Consecutives", oc_col = oc, "Longest Runs", oc_col <> oc), CHOOSE({1,2}, INDEX(fd, allr_fd, 1), oc_col), {"|","|"}),
rstr, CONCAT(INDEX(revfd, , 2)),
rary, IFERROR(FILTERXML("<x><y>" & SUBSTITUTE(rstr, "|", "</y><y>") & "</y></x>", "//y"), ""),
ls, LEN(rary),
maxs, MAX(ls),
nmaxs, SUM(--(ls = maxs)),
csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls),
idxs_ls, FILTER(SEQUENCE(ROWS(ls)), ls = maxs),
idxs_dts, INDEX(csum_ls, idxs_ls),
seds, INDEX(FILTER(INDEX(revfd, , 1), INDEX(revfd, , 1) <> "|"), idxs_dts),
dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")),
finres, IF(maxs = 0, {"none","",""}, CHOOSE({1,2,3}, maxs, nmaxs, dt_comp)), finres)

Here are the data used in the above minisheet:

MrExcel_20240321 (version 1) (version 1).xlsx
	A	B			E					J	K	L	M
1		Date			Location (B)					Match Type (A)	Result (C)	GF	GA
2		1-Feb			A					League	W	6	1
3		2-Feb			H					League	L	1	3
4		3-Feb			H					League	L	0	2
5		4-Feb			H					League	W	9	2
6		5-Feb			A					League	L	0	5
7		6-Feb			H					League	W	6	3
8		7-Feb			A					League	L	2	4
9		8-Feb			H					League	W	5	4
10		9-Feb			H					League	W	5	0
11		10-Feb			H					FA Cup	W	7	5
12		11-Feb			A					FA Cup	L	1	3
13		12-Feb			A					League	W	6	5
14		13-Feb			H					League	D	1	1
15		14-Feb			A					League	D	2	2
16		15-Feb			H					League	W	1	0
17		16-Feb			H					League	L	3	4
18		17-Feb			A					League	W	5	0
19		18-Feb			A					League	L	1	2
20		19-Feb			H					League	W	7	5
21		20-Feb			H					League	W	9	2
22		21-Feb			A					League	D	4	4
23		22-Feb			H					League	W	8	2
24		23-Feb			A					League	W	2	1
25		24-Feb			H					League	W	6	1
26		25-Feb			A					League	W	8	5
27		26-Feb			A					League	W	9	3
28		27-Feb			A					League	D	1	1
29		28-Feb			A					League	L	0	2
30		29-Feb			H					League	W	6	5
31		1-Mar			A					League	W	8	1
32		2-Mar			H					FA Cup	L	0	1
33		3-Mar			A					League	L	1	3
34		4-Mar			H					League	W	1	0
35		5-Mar			A					FA Cup	W	6	0
36		6-Mar			A					FA Cup	W	8	1
37		7-Mar			A					FA Cup	W	8	5
38		8-Mar			A					FA Cup	W	8	5
39		9-Mar			A					FA Cup	W	5	3
40		10-Mar			A					FA Cup	W	9	5
All

 

[Marvo]

Thank you. I've utilised the first formula, just changed it to 7000 as that is going to take me way past my personal sell-by-date and it will then fall on someone else to carry the torch.

The larger formula is tricky for me as my All-Time record boxes don't use any helper columns (I don't think), they are in the search boxes.

However I would like to put all your hard work to good use so in the mini-sheet, where does the formula go? Is it C4?

Cobblers.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	ALL-TIME RECORDS
2	CONSECUTIVES		W	Wins		D	Draws		L	Defeats		GF			GA
3			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
4	ALL	ALL	11	1	28/12/1899	6	2	1983 & 2011	7	4	1972, 1985, 1988 & 1992	28	1	03/10/1908	29	1	28/12/1965
5	Matches	H	14	1	16/04/1927	5	1	21/03/1981	6	2	1985 & 2002	54	1	28/01/0928	16	1	13/10/1900
6		A	6	2	2004 & 2015	4	2	1913 & 1994	16	1	27/10/1906	19	1	27/03/1959	40	1	01/09/1926
7	League	ALL	11	1	28/12/1899	6	2	1983 & 2011	8	1	26/10/1935	28	1	29/08/2015	27	1	28/09/1965
8	Matches	Home	12	2	1899 & 2027	5	1	21/03/1981	5	5	1905, 1985, 1999, 2002 & 2016	75	1	28/12/1926	19	1	13/10/1900
9		Away	8	1	21/11/2015	5	1	06/09/1994	15	1	27/10/1906	18	1	27/03/1959	42	1	20/02/1965
10	LONGEST RUNS		NO L			NO D			NO W			NO GA			NO GF
11			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
12	ALL	ALL	27	1	23/01/2016	29	1	05/02/1927	19	2	1969 & 1993	6	2	1910 & 1930	7	1	07/04/1939
13	Matches	H	33	1	22/02/1913	28	1	31/08/1957	11	1	17/01/1990	9	1	25/08/1923	6	1	14/12/2002
14		A	14	1	13/02/2016	21	1	12/04/1908	28	1	27/01/1906	6	1	12/04/1997	7	2	1938 & 1995
15	lge	ALL	31	1	28/12/2015	29	1	05/02/1927	18	2	1969 & 2011	6	2	1930 & 1975	7	1	07/04/1939
16	Matches	H	33	1	22/02/1913	26	2	1898 & 1984	11	1	17/01/1990	9	1	25/08/1923	5	1	14/12/2002
17		A	19	1	31/10/2015	23	1	30/04/1966	33	1	01/01/1921	6	1	12/04/1997	8	1	28/03/1967
18	SEARCH BOX (Enter amount of games required under TOTAL and date of most recent record will appear and how many times its happened)
19	CONSECUTIVES		W			D			L			GF			GA
20			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
21		ALL
22	ALL	H
23		A
24		ALL
25	LEAGUE	H
26		A
27	LONGEST RUNS		NO L			NO D			NO W			NO GA			NO GF
28			TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE	TOTAL	TIMES	START/END DATE
29		ALL
30	ALL	H
31		A
32		ALL
33	LEAGUE	H
34		A
Records

Cell Formulas
Range	Formula
C4	C4	=LET(t,ALL!$K2:$K7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=$C$2,s),IF(t<>$C$2,s))))
C5,I5,F5	C5	=LET(t,FILTER(ALL!$K2:$K7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C6,I6,F6	C6	=LET(t,FILTER(ALL!$K2:$K7000,ALL!$E2:$E7000=$B$6),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C7,I7,F7	C7	=LET(t,FILTER(ALL!$K2:$K7000,ALL!$J2:$J7000=$A$7),MAX(FREQUENCY(IF(t=C2,SEQUENCE(ROWS(t))),IF(t<>C2,SEQUENCE(ROWS(t))))))
C8	C8	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>"W",s))))
C9,I9	C9	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=C2,s),IF(t<>C2,s))))
F4	F4	=LET(t,ALL!$K2:$K7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F8	F8	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
F9	F9	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t="D",s),IF(t<>"D",s))))
I4	I4	=LET(t,ALL!K2:K7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=I2,s),IF(t<>I2,s))))
I8	I8	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=F2,s),IF(t<>F2,s))))
L4	L4	=LET(t,ALL!L2:L7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
L5	L5	=LET(t,FILTER(ALL!$L$2:$L$7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L6	L6	=LET(t,FILTER(ALL!$L$2:$L$7000,ALL!$E$2:$E$7000=$B$6),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L7	L7	=LET(t,FILTER(ALL!$L:$L,ALL!$J:$J="League"),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L8	L8	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
L9	L9	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O4	O4	=LET(t,ALL!$M2:$M7000,s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>0,s),IF(t=0,s))))
O5	O5	=LET(t,FILTER(ALL!M2:M7000,ALL!$E2:$E7000=$B$5),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O6	O6	=LET(t,FILTER(ALL!M2:M7000,ALL!E2:E7000=$B$6),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O7	O7	=LET(t,FILTER(ALL!$M2:$M7000,ALL!$J2:$J7000=$A$7),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O8	O8	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
O9	O9	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),MAX(FREQUENCY(IF(t<>0,SEQUENCE(ROWS(t))),IF(t=0,SEQUENCE(ROWS(t))))))
C12	C12	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C13	C13	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C14	C14	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C15	C15	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C16	C16	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
C17	C17	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>I2,s),IF(t=I2,s))))
F12	F12	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F13	F13	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F14	F14	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F15	F15	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F16	F16	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
F17	F17	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>F2,s),IF(t=F2,s))))
I12	I12	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I13	I13	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I14	I14	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I15	I15	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I16	I16	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
I17	I17	=LET(t,FILTER(ALL!$K2:$K7000,(ALL!$K2:$K7000<>"")*(ALL!$J2:$J7000=A7)*(ALL!$E2:$E7000=B6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t<>C2,s),IF(t=C2,s))))
L12	L12	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L13	L13	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L14	L14	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L15	L15	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")*(ALL!$J2:$J7000=$A$7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L16	L16	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")*(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
L17	L17	=LET(t,FILTER(ALL!$M2:$M7000,(ALL!$M2:$M7000<>"")*(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O12	O12	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O13	O13	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O14	O14	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O15	O15	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")*(ALL!$J2:$J7000=$A$7)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O16	O16	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")*(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$5)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
O17	O17	=LET(t,FILTER(ALL!$L2:$L7000,(ALL!$L2:$L7000<>"")*(ALL!$J2:$J7000=$A$7)*(ALL!$E2:$E7000=$B$6)),s,SEQUENCE(ROWS(t)),MAX(FREQUENCY(IF(t=0,s),IF(t<>0,s))))
D21	D21	=IF(C21="","",COUNTIFS(ALL!$AH$2:$AH$5558,C21))
E21	E21	=IF(C21="","",LET(XL,XLOOKUP(C21,ALL!$AH$2:$AH$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D22	D22	=IF(C22="","",COUNTIFS(ALL!$E$2:$E$5558,$B$22,ALL!$AM$2:$AM$5558,C22))
E22	E22	=IF(C22="","",LET(XL,XLOOKUP(C22,ALL!$AM$2:$AM$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D23	D23	=IF(C23="","",COUNTIFS(ALL!$E$2:$E$5558,$B$23,ALL!$AR$2:$AR$5558,C23))
E23	E23	=IF(C23="","",LET(XL,XLOOKUP(C23,ALL!$AR$2:$AR$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D24	D24	=IF(C24="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$AW$2:$AW$5558,C24))
E24	E24	=IF(C24="","",LET(XL,XLOOKUP(C24,ALL!$AW$2:$AW$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D25	D25	=IF(C25="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$25,ALL!$BB$2:$BB$5558,C25))
E25	E25	=IF(C25="","",LET(XL,XLOOKUP(C25,ALL!$BB$2:$BB$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D26	D26	=IF(C26="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$26,ALL!$BG$2:$BG$5558,C26))
E26	E26	=IF(C26="","",LET(XL,XLOOKUP(C26,ALL!$BG$2:$BG$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G21	G21	=IF(F21="","",COUNTIFS(ALL!$AI$2:$AI$5558,F21))
H21	H21	=IF(F21="","",LET(XL,XLOOKUP(F21,ALL!$AI$2:$AI$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G22	G22	=IF(F22="","",COUNTIFS(ALL!$E$2:$E$5558,$B$22,ALL!$AN$2:$AN$5558,F22))
H22	H22	=IF(F22="","",LET(XL,XLOOKUP(F22,ALL!$AN$2:$AN$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G23	G23	=IF(F23="","",COUNTIFS(ALL!$E$2:$E$5558,$B$23,ALL!$AS$2:$AS$5558,F23))
H23	H23	=IF(F23="","",LET(XL,XLOOKUP(F23,ALL!$AS$2:$AS$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G24	G24	=IF(F24="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$AX$2:$AX$5558,F24))
H24	H24	=IF(F24="","",LET(XL,XLOOKUP(F24,ALL!$AX$2:$AX$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G25	G25	=IF(F25="","",COUNTIFS(ALL!$J2:$J5558,A25,ALL!$E2:$E5558,B25,ALL!$BC$2:$BC$5558,F25))
H25	H25	=IF(F25="","",LET(XL,XLOOKUP(F25,ALL!$BC$2:$BC$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G26	G26	=IF(F26="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$26,ALL!$BH$2:$BH$5558,F26))
H26	H26	=IF(F26="","",LET(XL,XLOOKUP(F26,ALL!$BH$2:$BH$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J21	J21	=IF(I21="","",COUNTIFS(ALL!$AJ$2:$AJ$5558,I21))
K21	K21	=IF(I21="","",LET(XL,XLOOKUP(I21,ALL!$AJ$2:$AJ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J22	J22	=IF(I22="","",COUNTIFS(ALL!$E$2:$E$5558,$B$22,ALL!$AO$2:$AO$5558,I22))
K22	K22	=IF(I22="","",LET(XL,XLOOKUP(I22,ALL!$AO$2:$AO$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J23	J23	=IF(I23="","",COUNTIFS(ALL!$E$2:$E$5558,$B$23,ALL!$AT$2:$AT$5558,I23))
K23	K23	=IF(I23="","",LET(XL,XLOOKUP(I23,ALL!$AT$2:$AT$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J24	J24	=IF(I24="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$AY$2:$AY$5558,I24))
K24	K24	=IF(I24="","",LET(XL,XLOOKUP(I24,ALL!$AY$2:$AY$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J25	J25	=IF(I25="","",COUNTIFS(ALL!$J2:$J5558,$A$25,ALL!$E2:$E5558,$B$25,ALL!$BD$2:$BD$5558,I25))
K25	K25	=IF(I25="","",LET(XL,XLOOKUP(I25,ALL!$BD$2:$BD$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J26	J26	=IF(I26="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$26,ALL!$BI$2:$BI$5558,I26))
K26	K26	=IF(I26="","",LET(XL,XLOOKUP(I26,ALL!$BI$2:$BI$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M21	M21	=IF(L21="","",COUNTIFS(ALL!$AK$2:$AK$5558,L21))
N21	N21	=IF(L21="","",LET(XL,XLOOKUP(L21,ALL!$AK$2:$AK$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M22	M22	=IF(L22="","",COUNTIFS(ALL!$E$2:$E$5558,$B$22,ALL!$AP$2:$AP$5558,L22))
N22	N22	=IF(L22="","",LET(XL,XLOOKUP(L22,ALL!$AP$2:$AP$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M23	M23	=IF(L23="","",COUNTIFS(ALL!$E$2:$E$5558,$B$23,ALL!$AU$2:$AU$5558,L23))
N23	N23	=IF(L23="","",LET(XL,XLOOKUP(L23,ALL!$AU$2:$AU$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M24	M24	=IF(L24="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$AZ$2:$AZ$5558,L24))
N24	N24	=IF(L24="","",LET(XL,XLOOKUP(L24,ALL!$AZ$2:$AZ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M25	M25	=IF(L25="","",COUNTIFS(ALL!$J2:$J5558,$A$25,ALL!$E2:$E5558,$B$25,ALL!$BE$2:$BE$5558,L25))
N25	N25	=IF(L25="","",LET(XL,XLOOKUP(L25,ALL!$BE$2:$BE$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M26	M26	=IF(L26="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$26,ALL!$BJ$2:$BJ$5558,L26))
N26	N26	=IF(L26="","",LET(XL,XLOOKUP(L26,ALL!$BJ$2:$BJ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P21	P21	=IF(O21="","",COUNTIFS(ALL!$AL$2:$AL$5558,O21))
Q21	Q21	=IF(O21="","",LET(XL,XLOOKUP(O21,ALL!$AL$2:$AL$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P22	P22	=IF(O22="","",COUNTIFS(ALL!$E$2:$E$5558,$B$22,ALL!$AQ$2:$AQ$5558,O22))
Q22	Q22	=IF(O22="","",LET(XL,XLOOKUP(O22,ALL!$AQ$2:$AQ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P23	P23	=IF(O23="","",COUNTIFS(ALL!$E$2:$E$5558,$B$23,ALL!$AV$2:$AV$5558,O23))
Q23	Q23	=IF(O23="","",LET(XL,XLOOKUP(O23,ALL!$AV$2:$AV$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P24	P24	=IF(O24="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$BA$2:$BA$5558,O24))
Q24	Q24	=IF(O24="","",LET(XL,XLOOKUP(O24,ALL!$BA$2:$BA$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P25	P25	=IF(O25="","",COUNTIFS(ALL!$J2:$J5558,$A$25,ALL!$E2:$E5558,$B$25,ALL!$BF$2:$BF$5558,O25))
Q25	Q25	=IF(O25="","",LET(XL,XLOOKUP(O25,ALL!$BF$2:$BF$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P26	P26	=IF(O26="","",COUNTIFS(ALL!$J$2:$J$5558,$A$25,ALL!$E$2:$E$5558,$B$26,ALL!$BK$2:$BK$5558,O26))
Q26	Q26	=IF(O26="","",LET(XL,XLOOKUP(O26,ALL!$BK$2:$BK$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D29	D29	=IF(C29="","",COUNTIFS(ALL!$BL$2:$BL$5558,C29))
E29	E29	=IF(C29="","",LET(XL,XLOOKUP(C29,ALL!$BL$2:$BL$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D30	D30	=IF(C30="","",COUNTIFS(ALL!$E$2:$E$5558,$B$30,ALL!$BQ$2:$BQ$5558,C30))
E30	E30	=IF(C30="","",LET(XL,XLOOKUP(C30,ALL!$BQ$2:$BQ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D31	D31	=IF(C31="","",COUNTIFS(ALL!$E$2:$E$5558,$B$31,ALL!$BV$2:$BV$5558,C31))
E31	E31	=IF(C31="","",LET(XL,XLOOKUP(C31,ALL!$BV$2:$BV$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D32	D32	=IF(C32="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$CA$2:$CA$5558,C32))
E32	E32	=IF(C32="","",LET(XL,XLOOKUP(C32,ALL!$CA$2:$CA$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D33	D33	=IF(C33="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$33,ALL!$CF$2:$CF$5558,C33))
E33	E33	=IF(C33="","",LET(XL,XLOOKUP(C33,ALL!$CF$2:$CF$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
D34	D34	=IF(C34="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$34,ALL!$CK$2:$CK$5558,C34))
E34	E34	=IF(C34="","",LET(XL,XLOOKUP(C34,ALL!$CK$2:$CK$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G29	G29	=IF(F29="","",COUNTIFS(ALL!$BM$2:$BM$5558,F29))
H29	H29	=IF(F29="","",LET(XL,XLOOKUP(F29,ALL!$BM$2:$BM$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G30	G30	=IF(F30="","",COUNTIFS(ALL!$E$2:$E$5558,$B$30,ALL!$BR$2:$BR$5558,F30))
H30	H30	=IF(F30="","",LET(XL,XLOOKUP(F30,ALL!$BR$2:$BR$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G31	G31	=IF(F31="","",COUNTIFS(ALL!$E$2:$E$5558,$B$31,ALL!$BW$2:$BW$5558,F31))
H31	H31	=IF(F31="","",LET(XL,XLOOKUP(F31,ALL!$BW$2:$BW$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G32	G32	=IF(F32="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$CB$2:$CB$5558,F32))
H32	H32	=IF(F32="","",LET(XL,XLOOKUP(F32,ALL!$BC$2:$CB$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G33	G33	=IF(F33="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$33,ALL!$CG$2:$CG$5558,F33))
H33	H33	=IF(F33="","",LET(XL,XLOOKUP(F33,ALL!$CG$2:$CG$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
G34	G34	=IF(F34="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$34,ALL!$CL$2:$CL$5558,F34))
H34	H34	=IF(F34="","",LET(XL,XLOOKUP(F34,ALL!$CL$2:$CL$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J29	J29	=IF(I29="","",COUNTIFS(ALL!$BN$2:$BN$5558,I29))
K29	K29	=IF(I29="","",LET(XL,XLOOKUP(I29,ALL!$BN$2:$BN$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J30	J30	=IF(I30="","",COUNTIFS(ALL!$E$2:$E$5558,$B$30,ALL!$BS$2:$BS$5558,I30))
K30	K30	=IF(I30="","",LET(XL,XLOOKUP(I30,ALL!$BS$2:$BS$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J31	J31	=IF(I31="","",COUNTIFS(ALL!$E$2:$E$5558,$B$31,ALL!$BX$2:$BX$5558,I31))
K31	K31	=IF(I31="","",LET(XL,XLOOKUP(I31,ALL!$BX$2:$BX$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J32	J32	=IF(I32="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$CC$2:$CC$5558,I32))
K32	K32	=IF(I32="","",LET(XL,XLOOKUP(I32,ALL!$CC$2:$CC$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J33	J33	=IF(I33="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$33,ALL!$CH$2:$CH$5558,I33))
K33	K33	=IF(I33="","",LET(XL,XLOOKUP(I33,ALL!$CH$2:$CH$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
J34	J34	=IF(I34="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$34,ALL!$CM$2:$CM$5558,I34))
K34	K34	=IF(I34="","",LET(XL,XLOOKUP(I34,ALL!$CM$2:$CM$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M29	M29	=IF(L29="","",COUNTIFS(ALL!$BO$2:$BO$5558,L29))
N29	N29	=IF(L29="","",LET(XL,XLOOKUP(L29,ALL!$BO$2:$BO$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M30	M30	=IF(L30="","",COUNTIFS(ALL!$E$2:$E$5558,$B$30,ALL!$BT$2:$BT$5558,L30))
N30	N30	=IF(L30="","",LET(XL,XLOOKUP(L30,ALL!$BT$2:$BT$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M31	M31	=IF(L31="","",COUNTIFS(ALL!$E$2:$E$5558,$B$31,ALL!$BY$2:$BY$5558,L31))
N31	N31	=IF(L31="","",LET(XL,XLOOKUP(L31,ALL!$BY$2:$BY$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M32	M32	=IF(L32="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$CD$2:$CD$5558,L32))
N32	N32	=IF(L32="","",LET(XL,XLOOKUP(L32,ALL!$CD$2:$CD$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M33	M33	=IF(L33="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$33,ALL!$CI$2:$CI$5558,L33))
N33	N33	=IF(L33="","",LET(XL,XLOOKUP(L33,ALL!$CI$2:$CI$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
M34	M34	=IF(L34="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$34,ALL!$CN$2:$CN$5558,L34))
N34	N34	=IF(L34="","",LET(XL,XLOOKUP(L34,ALL!$CN$2:$CN$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P29	P29	=IF(O29="","",COUNTIFS(ALL!$BP$2:$BP$5558,O29))
Q29	Q29	=IF(O29="","",LET(XL,XLOOKUP(O29,ALL!$BP$2:$BP$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P30	P30	=IF(O30="","",COUNTIFS(ALL!$E$2:$E$5558,$B$30,ALL!$BU$2:$BU$5558,O30))
Q30	Q30	=IF(O30="","",LET(XL,XLOOKUP(O30,ALL!$BU$2:$BU$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P31	P31	=IF(O31="","",COUNTIFS(ALL!$E$2:$E$5558,$B$31,ALL!$BZ$2:$BZ$5558,O31))
Q31	Q31	=IF(O31="","",LET(XL,XLOOKUP(O31,ALL!$BZ$2:$BZ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P32	P32	=IF(O32="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$CE$2:$CE$5558,O32))
Q32	Q32	=IF(O32="","",LET(XL,XLOOKUP(O32,ALL!$CE$2:$CE$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P33	P33	=IF(O33="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$33,ALL!$CJ$2:$CJ$5558,O33))
Q33	Q33	=IF(O33="","",LET(XL,XLOOKUP(O33,ALL!$CJ$2:$CJ$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))
P34	P34	=IF(O34="","",COUNTIFS(ALL!$J$2:$J$5558,$A$33,ALL!$E$2:$E$5558,$B$34,ALL!$CO$2:$CO$5558,O34))
Q34	Q34	=IF(O34="","",LET(XL,XLOOKUP(O34,ALL!$CO$2:$CO$5558,ALL!$B$2:$B$5558,,0,-1),HYPERLINK(CELL("address",XL),XL)))

 

[KRice]

Yes, delete the results in the table, as the formula will need empty cells for spilling the result arrays. One note about column A where All or League appears: Because those words are not on every line where needed, the formula assumes that the word appears in the uppermost cell where it applies, and then the formula carries down that same value until until it changes. So you may need to delete anything else in those column A cells. And if you have vertically merged cells there, just confirm that the uppermost of those merged cells contains All or League. In the example you posted, All would be in A4, then nothing in A5:A6 (you can merge those cells I believe, and All will appear to be centered, but actually still reside in A4. The formula then "sees" A4=All and applies "All" until the value changes in A7, where "League" would appear...followed by blanks in A8:A9 (again, those three cells can be merged, and "League" will appear centered, but actually reside in A7). Then the formula would be pasted into C4:C9, C12:C17, F4:F9, F12:F17, etc...everywhere directly below the outcome codes (W, NO L, etc.).

The formula also assumes that "Consecutives" and "Longest Runs" appear on the same row as the outcome codes, so confirm those are on rows 2 and 10 (again, those appear to be vertically merged cells, but I believe the values appear the same rows as the W, D, L.... outcome codes.

 

[Marvo]

I tired to put the formula into C4 but I get the message

"We cant do that to a merged cell".

Of course C4 isn't a merged cell so.........

 

[KRice]

That’s odd. Did you copy just the formula to the clipboard and attempt to paste it only into C4? Or did you have multiple cells selected?

 

[Marvo]

Just C4.