Given a list of sequential, variable-length (rx1) arrays, dynamically stack the arrays (vertically) such that they follow a particular set of rules -- ordered first by the Order column in the States Table then by the Order column in the Classifications Table; skip 1 space after a change in class and 2 spaces after a change in State; ignore blanks; work around control panel column supplemental rules in output range (example output 1).
The objective is to produce the output shown here, as an array:
The file can be downloaded here. Thank you!
Edit: Solved it. Updated data file with solution.
Given that I've solved it, I figured I'd publicly inter this pitiful, twice-downvoted question with the solution and some interesting findings.
This took a variety of helper functions to first process the array, iterate through it column by column, then process it again.
The first:
SHORTEN =
lambda(_arr, [_applyToArr],
LET(
_applyTo, IF(ISOMITTED(_applyToArr), _arr, _applyToArr),
FILTER(_applyTo,
BYCOL(_arr, LAMBDA(a, IF(LEN(CONCAT(a))<>0,TRUE,FALSE)))
)
)
);
The second:
PROCESSARRAY =
LAMBDA(_arr, _header, _StatesTable, _ClassTable,
LET(
_reducedArray, SHORTEN(_arr),
_reducedHeader, SHORTEN(_arr, _header),
_rankedHeader,
MAP(_reducedHeader,
LAMBDA(v,
XLOOKUP(v,
CHOOSECOLS(_StatesTable, 2), CHOOSECOLS(_StatesTable, 1),
XLOOKUP(v,
CHOOSECOLS(_ClassTable, 2), CHOOSECOLS(_ClassTable, 1), "Err", 0, 1)
, 0, 1)
)
),
_stackedArray, VSTACK(_rankedHeader, _reducedArray),
_sortedArray, SORTBY(_stackedArray, TAKE(_stackedArray, 1), 1, TAKE(DROP(_stackedArray, 1), 1), 1),
return, _sortedArray,
return
)
);
The third:
ENTITYSTACKER =
LAMBDA(_arr, _header, _StatesTable, _ClassTable,
LET(
_processedArray, PROCESSARRAY(_arr, _header, _StatesTable, _ClassTable),
_processedHeader, TAKE(_processedArray, 2),
_processedArrayBody, DROP(_processedArray, 2),
_rng, COLUMNS(_processedArray),
_seq, SEQUENCE(_rng, 1),
_combinedArray,
VSTACK(
REDUCE("", _seq,
LAMBDA(a,v,
IF(v = 1, ENTITYSTACKER_INNER(INDEX(_processedArray,,v)),
HSTACK(a, ENTITYSTACKER_INNER(INDEX(_processedArray,,v), INDEX(_processedHeader,,v-1)))
)
)
)
),
_stackedArray, TOCOL(_combinedArray, 2, TRUE),
return, _stackedArray,
return
)
);
The fourth:
ENTITYSTACKER_INNER =
LAMBDA(_currCol, [_priorCol],
LET(
_currColRank, TAKE(_currCol, 1),
_priorColRank, IF(ISOMITTED(_priorCol), NA(), TAKE(_priorCol, 1)),
_currColBody, FILTER(DROP(_currCol, 2), DROP(_currCol, 2)<>"", ""),
_curColPaddedBody,
IF(ISOMITTED(_priorCol), _currColBody,
IF(_currColRank = _priorColRank,
VSTACK({""}, _currColBody), VSTACK({""}, {""}, _currColBody)
)
),
return, _curColPaddedBody,
return
)
);
And finally, the fifth:
CONTROLPANEL =
LAMBDA(_arr, _controls,
LET(
_totalSkips, COUNTIF(_controls, "S"),
_seq, SEQUENCE(ROWS(_arr)),
_fullSeq, SEQUENCE(SUM(_totalSkips, TAKE(_seq, -1))),
_output,
REDUCE("", _fullSeq,
LAMBDA(a,v,
LET(
_minV, MIN(v, TAKE(_seq, -1)),
_skips, COUNTIF(TAKE(_controls, v), "S"),
_index, v - _skips,
IF(v = 1,
IFS(INDEX(_controls, v) = "S", "",
INDEX(_controls, v) = "O", "",
TRUE, INDEX(_arr, _index)),
IFS(INDEX(_controls, v) = "S", VSTACK(a, ""),
INDEX(_controls, v) = "O", VSTACK(a, ""),
TRUE, VSTACK(a, INDEX(_arr, _index)))
)
)
)
),
return, _output,
return
)
);
The final formula for the example file reads as such:
=CONTROLPANEL(ENTITYSTACKER(J14#,J12#,States2,Classifications2),$C$31:$C$63)