Excel Formula to Stack a Variable Number of Variable-Length Arrays with Conditional Spacing

Question

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.

Answer 1

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)))
        )
    )
);

• This takes a 2D array and returns the same 2D array with empty columns omitted. Optionally, it can also take a second array, 2D or otherwise, and apply the reduction of columns based off of the same results to that array instead.

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
    )
);

• This function first calls SHORTEN()
• It then converts a 2xc cartesian product header into an ordinal ascending output, stacked above its corresponding data.

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
    )   
);

• This function first calls PROCESSARRAY()
• It then traverses through the 2D array column by column, sending each to ENTITYSTACKER_INNER to apply conditional functions.
• Finally, it vertically stacks the output, ignoring NA()s but keeping blanks.

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
    )
);

• Using the now-ordinal rankings, this function pads each column with either one (to indicate a shift in rank 2) or two (to indicate a shift in rank 1) empty cells. Given that the function is called by it's parent ENTITYSTACKER(), the output is embedded in the image above.

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
    )
);

• This final function is perhaps the most interesting. It inserts spaces into a vertical array based on specified controls in the corresponding cells of a "control panel" array. In this case, "S" indicates skip, "O" indicates omit, and any other cell value has no impact on the array.

The final formula for the example file reads as such:

=CONTROLPANEL(ENTITYSTACKER(J14#,J12#,States2,Classifications2),$C$31:$C$63)