Array Transformation functions

ACOMBINE

Reference

» Reference, Discussion, & Example Applications:

About

Lists all possible combinations between all unique elements found on each column of an array.

In other words - produces a cartesian product!

Calls AUNIQUE, ACLEAN, and AXLOOKUP.

Inputs:

• a : array
• cl : array of column indexes, if ignored, entire array will be considered, ex: {1, 3} or {2, 1} or {3, 1, 2}

Code

ACOMBINE = LAMBDA(a, cl,
    LET(
        y, INDEX(a, SEQUENCE(ROWS(a)), IF(AND(cl), cl, SEQUENCE(, COLUMNS(a)))),
        u, AUNIQUE(y, -1),
        v, TRANSPOSE(AUNQSRT(TRANSPOSE(u), 1)),
        r, ROWS(v),
        c, COLUMNS(v),
        s, SEQUENCE(, c),
        x, MOD(ROUNDUP(SEQUENCE(r ^ c) / r ^ ABS(s - c), 0) - 1, r) * c + s,
        ACLEAN(AXLOOKUP(x, IF(v = "", 0, SEQUENCE(r, c)), v, , , ), 1)
    )
);