Custom Function to Cross-Joining

Question

I am looking for a flexible custom function to be able to cross-join (cartesian product) 2 columns of varying lengths in Google Sheets.

My research so far has led me to ask because:

1. using a combination of existing formulas in Sheets either produces some weird artifacts (counting blank cells as values to cross-join) or has limits to how many results it can produce.
2. The JavaScript solutions online pertain to specific examples and have not worked for my case yet (I have very limited knowledge of JavaScript).

To further describe my request:

1. It must be able to cross-join 2 columns of ANY length (if one column is longer than the other, I don't want it to utilize blank cells from the shorter one).
2. I would love it to be flexible in that I can place it anywhere and use any combination of columns

Edit 1:
Sorry for any missing information, it's my first time posting here

So I'm not sure how many formulas I have tried but it's been over 20, I'm not sure how productive it would be to post those in here because I'm looking for JavaScript at this point.

Here's an example of some data sets and results I would like to get from this one script:

I've seen many scripts be dependent on the header name and stuff and I would like that not to be the case.

Answer 1

You can get that with a custom function, but it would probably make more sense to use a plain vanilla spreadsheet formula.

The example results you show is row-by-row concatenation rather than a Cartesian product of two sets. To get row-by-row concatenation with a spreadsheet formula, ignoring blank values, use this:

=arrayformula( flatten( filter(A2:A, len(A2:A)) & " " & transpose(filter(B2:B, len(B2:B))) ) )

To get an actual Cartesian product, ignoring blank values, use split() like this:

=arrayformula( split( flatten( filter(A2:A, len(A2:A)) & "µ" & transpose(filter(B2:B, len(B2:B))) ), "µ" ) )

You can handle more than two ranges using the same flatten(transpose()) pattern.

The question explicitly specifies JavaScript, so here's a custom function to do the same:

/**
* Creates a cross join of two or more ranges.
*
* The result is a Cartesian product of the input ranges,
* ignoring any blank values.
*
* @param {A2:A} range1 The first range to join.
* @param {B2:B} range2 The second range to join.
* @param {C2:C} range3 Optional. Any additional ranges to join.
* @return Cartesian product of the ranges, ignoring any blank values.
* @customfunction
*/
function CartesianProduct(range1, range2, range3) {
  // @version 1.0, written by --Hyde, 27 August 2021
  // @see https://webapps.stackexchange.com/a/158699/269219
  // @license https://hyde.mit-license.org/2021
  if (arguments.length < 2) {
    throw new Error('CartesianProduct expected at least two parameters but only got ' + arguments.length + '.');
  }
  let numResults = 1;
  const args = ([...arguments].map(arg => !Array.isArray(arg) ? [[arg]] : !Array.isArray(arg[0]) ? [arg] : arg))
    .map(arg => {
      arg = arg.filter(row => row.join(''));
      if (!arg.length) {
        throw new Error('CartesianProduct expected all parameters to have at least one non-blank value.');
      }
      numResults *= arg.length;
      if (numResults > 1e6) {
        throw new Error('CartesianProduct found ' + numResults + ' results, which is too many to handle comfortably.');
      }
      return arg;
    });
  const cartesian_ = (a, b) => [].concat(...a.map(row => b.map(b => [].concat(row, b))));
  const iterate_ = (a, b, ...rest) => b ? iterate_(cartesian_(a, b), ...rest) : a;
  return iterate_(...args);
}

To use the custom function to do row-by-row concatenation instead of a Cartesian product, process the results with join().

Answer 2

The following custom functions have been made available in the formulas_by_top_contributors Addon:

=ARRAYFORMULA(
 MATRIX(SQLCROSSJOIN(A1:A4,1,B1:B2,2,FALSE),,1)
 &" "&
 MATRIX(SQLCROSSJOIN(A1:A4,1,B1:B2,2,FALSE),,2)
)

disclaimer: created and maintain the Addon