Array Transformation functions

AUNQSRT

Reference

» Reference, Discussion, & Example Applications:

About

Sorts ascending, descending, or extracts unique values by rows of an array, left aligned.

Inputs:

• a : required array
• k : -1 sort descending; 0 unique; 1 sort ascending

Code

AUNQSRT = LAMBDA(ar, k,
    LET(
        xk, OR(k = {-1, 0, 1}),
        r, ROWS(ar),
        c, COLUMNS(ar),
        sr, SEQUENCE(r),
        s, SEQUENCE(r * c),
        q, QUOTIENT(s - 1, c) + 1,
        m, MOD(s - 1, c) + 1,
        a, INDEX(IF(ar = "", "", ar), q, m),
        x, a <> "",
        qf, FILTER(CHOOSE({1, 2}, q, a), x),
        y, SWITCH(k, -1, SORT(qf, {1, 2}, {1, -1}), 0, UNIQUE(qf), 1, SORT(qf, {1, 2})),
        na, INDEX(y, , 2),
        nq, INDEX(y, , 1),
        fq, FREQUENCY(nq, sr),
        p, INDEX(fq, sr),
        nc, MAX(p),
        nsa, IF(p >= SEQUENCE(, nc), SEQUENCE(r, nc)),
        nsr, SMALL(nsa, SEQUENCE(SUM(p))),
        rs, IFNA(XLOOKUP(nsa, nsr, na), ""),
        IF(xk, rs, "check var -1 (desc), 0 (unique), 1 (asc)")
    )
);