CODEWITHSUNDEEP
google-sheetscartesian-productcross-joinnamed-function
Iceman6425
Iceman6425

Reputation: 1

Modify an existing named function that returns a Cartesian product / cross join by adding an argument that specifies the # of columns/values per row

I found the following Google Sheets named function CARTESIAN_PRODUCT as an answer to a related question Here:

=IF(COLUMNS(range) = 1, IFNA(FILTER(range, range <> "")), LAMBDA(sub_product, last_col, REDUCE(, SEQUENCE(ROWS(sub_product)), LAMBDA(acc, cur, LAMBDA(new_range, IF(cur = 1, new_range, {acc; new_range}))({ARRAYFORMULA(IF(SEQUENCE(ROWS(last_col)), INDEX(sub_product, cur,))), last_col}))))(CARTESIAN_PRODUCT(ARRAY_CONSTRAIN(range, ROWS(range), COLUMNS(range) - 1)), LAMBDA(r, IFNA(FILTER(r, r <> "")))(INDEX(range,, COLUMNS(range)))))

This function has 1 argument, range, which specifies the columns with the values, and returns a Cartesian product / cross join with the same number of columns as are included in the range:

Example

I would like modify this named function by adding an argument that specifies the # of columns/values per row. For example, I'd like to be able to take the same range as in the image above and return 2 columns instead of 3:

Desired Result

I found a similar pair of named functions that work together to return all unique combinations from a single column (which I know is not a Cartesian product / cross join) and that include an additional argument, r, that specifies the # of columns/values per row Here:

COMBINATIONS_INDICES:

=LAMBDA(f_range; LAMBDA(f_range_rows; IF(OR(r <= 0; r > f_range_rows);; IF(r = f_range_rows; SEQUENCE(1; r); LAMBDA(n; max_inds; REDUCE(SEQUENCE(1; r); SEQUENCE(PRODUCT(SEQUENCE(n)) / PRODUCT(SEQUENCE(n - r)) / PRODUCT(SEQUENCE(r)) - 1); LAMBDA(acc; cur; {acc; LAMBDA(ind; IF(ind = 1; SEQUENCE(1; r; INDEX(acc; ROWS(acc); 1) + 1); {ARRAY_CONSTRAIN(INDEX(acc; ROWS(acc);); 1; ind - 1)\ SEQUENCE(1; r - ind + 1; INDEX(acc; ROWS(acc); ind) + 1)}))(MATCH(2; ARRAYFORMULA(1 / (max_inds - INDEX(acc; ROWS(acc);) > 0))))})))(f_range_rows; SEQUENCE(1; r; f_range_rows - r + 1)))))(ROWS(f_range)))(FLATTEN(range))

and COMBINATIONS:

=LAMBDA(comb_inds; IF(comb_inds = "";; LAMBDA(f_range; MAP(comb_inds; LAMBDA(i; INDEX(f_range; i))))(FLATTEN(range))))(COMBINATIONS_INDICES(range; r))

Example

So far I've been unsuccessful in my attempts to add an argument like what can be found in the COMBINATIONS_INDICES and COMBINATIONS functions that specifies the # of columns/values per row to the CARTESIAN_PRODUCT function.

Can this be done?

Edit:

Here is a screenshot of how the result would look like if we had 4 columns and wanted to restrict it to 2 and 3 columns.

Upvotes: 0

Views: 109

Answers (1)

z..
z..

Reputation: 12823

Try out this named function:

=IFERROR(FILTER(SPLIT(REDUCE(,SEQUENCE(1,COLUMNS(range)),LAMBDA(a,c,FLATTEN(a&"ζ"&TRANSPOSE(FILTER(INDEX(range,,c),INDEX(range,,c)<>""))))),"ζ"),TRANSPOSE(QUERY({SEQUENCE(cols);SEQUENCE(COLUMNS(range)-cols,1,0,0)},"where Col1 is not null"))),NA())

The arguments are range and cols.

Upvotes: 0

Related Questions