For the sake of keeping the data source to some manageable size while testing out various input combinations, I've put together this small working example. Details of the formula components follow. If this delivers expected results it can be readily adapted to your summary tables.
The first several expressions inside the LET function involved mapping certain ranges to the formula. Aside from the main source data, the queries depend on knowing four inputs: the match location [(H)ome or (A)way], the match type [League or All], the match outcome used to filter results [(W)in or (L)oss or (D)raw], and the calculation type indicating whether a consecutive string of outcomes is sought [wOnly...with only the specified outcomes] or whether a consecutive string of outcomes is sought that excludes the opposite outcome of that specified [woOpp...without the opposite outcome]. These four inputs are found (mostly) in helper lookup cells, which may be hidden after the initial setup:
Excel Formula:
loc, N1,
mt, N2,
oc, N3,
ct, N4,
For testing/debugging purposes, I listed the four inputs down a column, but in practice, loc is integral to the summary table and its value appears on every row (so a formula can read this, making a separate cell unnecessary). The other inputs do require lookup helper cells because the summary table provides clues about what they should be, but the table structure isn't convenient to use the existing row/column headings, as those headings are not present on every row/column. Therefore, mt and ct would be shown in row-by-row lookup cells, and oc is shown above the summary table columns containing the formula.
This calculation type (ct) idea requires that we define what an "opposite" outcome is, and the formula includes this lookup formula:
Excel Formula:
opp, XLOOKUP(oc,{"W","L","D"},{"L","W","D"})
In practice, this means we might want one of the following six streaks:
- with only consecutive Wins, or longest runs without a Loss
- with only consecutive Losses, or longest runs without a Win
- with only consecutive Draws, or longest runs without a Draw
So declaring the consecutive outcome and then declaring the calculation type (either wOnly or woOpp) determines how the data are filtered, and each of these six combinations maps to a subsection of the table. You may very well find more appealing descriptors for these two flavors of calculation types.
The source data is specified as a single overly large range reference, beginning with the 1st row of data, not the column headings, and the column containing match outcomes is used to filter out blanks, which produces a consolidated array of source data called fsrc. Further, fsrc is trimmed to include only the columns needed, establishing a 4-column array called "data". The four columns, in order, are {Date, Location, Match Type, Outcome}.
Excel Formula:
src, $B2:$K10000,
fsrc, FILTER(src,INDEX(src,,10)<>""),
data, INDEX(fsrc,SEQUENCE(ROWS(fsrc)),{1,4,9,10}),
For convenience, if all rows of data are needed, a variable called allr_data is established, consisting of a column of 1's.
Excel Formula:
allr_data, SEQUENCE(ROWS(data), , 1, 0),
Then filtering arrays for location and match type are created, either using all rows (if "All" is specified) or matching the input cells:
Excel Formula:
loc_ary, IF(loc = "All", allr_data, INDEX(data, , 2) = loc),
mt_ary, IF(mt = "All", allr_data, INDEX(data, , 3) = mt),
Next, the "data" array is filtered using the loc and mt criteria, and retaining only two columns (for Date and Match Outcome):
Excel Formula:
fd, FILTER(INDEX(data, SEQUENCE(ROWS(data)), {1, 4}), loc_ary * mt_ary),
For reasons that I do not entirely understand, it seems necessary to explicitly describe the rows that need to be used at one place in the next step, so for convenience, an array of row indexes for fd is created:
Excel Formula:
allr_fd, SEQUENCE(ROWS(fd)),
In the next step, fd is revised to overwrite certain entries that serve to break up the fd array of match dates/outcomes into streaks of the desired type. The calculation type (ct) is used to determine whether the comparison should only consider outcomes matching oc, or exclude outcomes not equal to the "opposite" outcome. If this outcome satisfies the criteria, that row in fd remains unchanged, otherwise it is replaced with pipe "|" characters, indicating a break between streaks, and this revised array is called revfd.
Excel Formula:
revfd, IF( SWITCH(ct, "wOnly", INDEX(fd, , 2) = oc, "woOpp", INDEX(fd, , 2) <> opp), INDEX(fd, allr_fd, {1, 2}), {"|", "|"} ),
The 2nd column of revfd (the match results meeting the required criteria) is concatenated, yielding strings of match outcomes separated by "|" characters (rstr). It is critical that the match outcomes in data consist of a single letter, as this method depends on examining the length of the text strings. The results string rstr is passed to the FILTERXML function, where it is split apart using the "|" character as a delimiter. This produces a single column array of streak results called rary, each element consisting of either a blank or some string of outcomes associated with a streak (WWDW for example). The length of each streak is determined based on the length of each text string in rary, producing a single column array called ls, which consists of numbers indicating the length of each streak (for example, {0;0;3;3}. From ls, we can readily obtain the maximum streak length (maxs) and the number of times such a streak occurred (nmaxs).
Excel Formula:
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)),
Determining when the streaks occurred is more challenging. I've used a method based on finding the cumulative sum of the ls array. For any given index position (row) in the ls array, the cumulative sum represents the number of dates from the top of the first column in the revfd array, so these individual cumulative sums can be used to retrieve the end date for any streak in revfd. Here is an Excel 2021-compatible method for the cumulative sum of the ls column array.
Excel Formula:
csum_ls, MMULT(--(SEQUENCE(1, ROWS(ls)) <= SEQUENCE(ROWS(ls))), ls),
To find the end date for all streaks having a length of maxs, we determine the row indexes in ls where the maxs value is found, and call this likely shortened array idxs_ls. The idxs_ls array contains an entry for every streak of max length. Then the cumulative sum that corresponds to the idxs_ls values is extracted from the csum_ls array, and this new array is called idxs_dts. Then, returning to the revfd array where the streaks had been broken up using the "|" character, we take the first column (dates), filter it to remove the "|" character, and use the idxs_dts values as row indexes to extract an array of streak end dates, called seds.
Excel Formula:
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),
There may be several dates in the seds array, so they are joined together, or compiled, into a text string called dt_comp using TEXTJOIN so that all dates can be delivered to a single cell. Due to concerns with the cell becoming overloaded, only the last streak end date is shown in dd/mm/yyyy format...earlier dates are shown in yyyy format. And an error trap is used to address the situation when only one date is in sed.
Excel Formula:
dt_comp, TEXTJOIN(", ", , IFERROR(TEXT(INDEX(seds, SEQUENCE(nmaxs - 1)), "yyyy"), ""), TEXT(INDEX(seds, nmaxs), "dd/mm/yyyy")),
Finally, all results for the sub-block row are delivered in a single spilling array:
Excel Formula:
IF(maxs = 0, {"none", "", ""}, CHOOSE({1, 2, 3}, maxs, nmaxs, dt_comp))
| MrExcel_20240321 (version 1).xlsx |
|---|
|
|---|
| B | | | E | | | | | J | K | L | M | N | O | P | Q | R |
|---|
| 1 | Date | | | Location | | | | | Match Type | Result | | match location | All | | TOTAL | TIMES | DATES |
|---|
| 2 | 1-Feb | | | A | | | | | League | W | | match type | League | | 3 | 2 | 2024, 09/02/2024 |
|---|
| 3 | 2-Feb | | | H | | | | | League | L | | outcomes | L | | | | |
|---|
| 4 | 3-Feb | | | H | | | | | League | L | | calc type | wOnly | | | | |
|---|
| 5 | 4-Feb | | | H | | | | | FA Cup | D | | | | | | | |
|---|
| 6 | 5-Feb | | | A | | | | | League | L | | | | | | | |
|---|
| 7 | 6-Feb | | | H | | | | | League | W | | | | | | | |
|---|
| 8 | 7-Feb | | | A | | | | | League | L | | | | | | | |
|---|
| 9 | 8-Feb | | | H | | | | | League | L | | | | | | | |
|---|
| 10 | 9-Feb | | | H | | | | | League | L | | | | | | | |
|---|
| 11 | 10-Feb | | | H | | | | | FA Cup | W | | | | | | | |
|---|
| 12 | 11-Feb | | | A | | | | | FA Cup | L | | | | | | | |
|---|
| 13 | 12-Feb | | | A | | | | | League | W | | | | | | | |
|---|
| 14 | 13-Feb | | | H | | | | | League | D | | | | | | | |
|---|
| 15 | 14-Feb | | | A | | | | | League | D | | | | | | | |
|---|
| 16 | 15-Feb | | | H | | | | | League | W | | | | | | | |
|---|
| 17 | | | | | | | | | | | | | | | | | |
|---|
|
|---|
