Prodigus
Prodigus

Reputation: 65

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.

Upvotes: -2

Views: 194

Answers (1)

Prodigus
Prodigus

Reputation: 65

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.

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
    )
);
  • This function first calls SHORTEN()
  • It then converts a 2xc cartesian product header into an ordinal ascending output, stacked above its corresponding data.

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

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

Example 4

The final formula for the example file reads as such:

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

Upvotes: 2

Related Questions