arraysexcelmultidimensional-arraylambdalambda-calculus

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


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

Demo of Workbook

The objective is to produce the output shown here, as an array: enter image description here

The file can be downloaded here. Thank you!

Edit: Solved it. Updated data file with solution.


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

    Example 1

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

    Example 2

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

    Example 3

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

    Example 4

    The final formula for the example file reads as such:

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