Array ‘By Element’ functions

SPILLBYROWS

Reference

» Reference, Discussion, & Example Applications:

About

Applies a helper lambda function by row, on a 2-D array.

Inputs:

• ar : array
• fn : function lambda helper argument LAMBDA(x, fn(x))
• [er] : error message argument, if the function delivers no results

Code

SPILLBYROWS = LAMBDA(ar,fn,[er],
    LET(
        e, IF(ISOMITTED(er), NA(), er),
        a, IF(ar = "", "", ar),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(a)),
            LAMBDA(v,i,
                LET(
                    x, INDEX(a, i, ),
                    y, IFERROR(
                        IF(
                            COLUMNS(x) = 1,
                            fn(INDEX(a, i, 1)),
                            fn(FILTER(x, x <> ""))
                        ),
                        ""
                    ),
                    VSTACK(v, y)
                )
            )
        ),
        d, DROP(IFNA(r, ""), 1),
        IF(AND(d = ""), e, d)
    )
);