Array Transformation functions

ACLEAN

Reference

» Reference, Discussion, & Example Applications:

About

Cleans an array, replaces errors with null strings or removes the rows with errors or removes the rows with errors and blanks or null strings.

Calls AUNIQUE.

Inputs:

• a : array
• k : 0 replaces errors with null strings; 1 removes only rows with errors; 2 removes rows with errors and blanks or null strings

Code

ACLEAN = LAMBDA(a, k,
    LET(
        xk, OR(k = {0, 1, 2}),
        r, ROWS(a),
        sr, SEQUENCE(r),
        x, ISERROR(a) * sr,
        y, sr * IFERROR(x + (a = ""), 1),
        z, AUNIQUE(SWITCH(k, 0, 0, 1, x, 2, y), ),
        xm, ISNA(XMATCH(sr, z)),
        IF(
            xk,
            IFERROR(FILTER(IF(a = "", "", a), xm), ""),
            "0 null strings for errors, 1 removes errors only, 2 removes errors and blanks"
        )
    )
);