(I will post my own answer, but I want to pose the question for the sake of others in a similar situation; and to encourage alternative answers.)
Sometimes I have two tables and I need to cross join them, i.e., generate the combinations of each row of the first table with each row of the second table. For example, suppose I have one table of chefs and the dishes they plated for a competition, and a table of evaluators and the attributes that each specializes on. (I'll specify them as formulas for easy pasting into Google Sheets.)
table1 = {
{ "Albion" , "Artichoke Soufflé Omelett" };
{ "Burgess" , "Lemony Braised Chicken" };
{ "Hamad" , "Mabo Dofu Smoothie" };
{ "Berengari" , "Chicken-Fried Plantains" };
{ "Sengupta" , "Smoky Vegan Corn Salad" }
}
table2 = {
{ "Cho" , "flavor" };
{ "Nikkelson" , "texture" };
{ "Rodríguez" , "process" }
}
Then the cross join of these two tables has 15 rows beginning with:
output = {
{ "Albion" , "Artichoke Soufflé Omelett" , "Cho" , "flavor" }
{ "Albion" , "Artichoke Soufflé Omelett" , "Nikkelson" , "texture" }
{ "Albion" , "Artichoke Soufflé Omelett" , "Rodríguez" , "process" }
{ "Burgess" , "Lemony Braised Chicken" , "Cho" , "flavor" }
{ "Burgess" , "Lemony Braised Chicken" , "Nikkelson" , "texture" }
{ "Burgess" , "Lemony Braised Chicken" , "Rodríguez" , "process" }
...
}
I am looking for a formula or Named Function to do this — not Google Apps Script. In addition, I want to avoid "string-hacking" methods in which you serialize arrays into strings with weird delimiters, then do text manipulation and concatenation that generates the desired structure, and finally deserialize into the reshaped array. As doubleunary pointed out in their answer to a similar question, such methods have side effects when they convert numeric types to strings. Personally I found they can have unpredictable behavior if any of the data contain certain emoji. I also find them difficult to troubleshoot and maintain.
My question is similar to the three below, but they were only about cross joining single-column tables.
There is a previous question that doesn't ask about multi-column tables explicitly, but includes one in its sample data; but the OP of the question allowed Apps Script:
This question might be similar, but I can't tell because the OP did not include sample data in the question and the linked spreadsheet no longer exists.
As per ...to avoid "string-hacking" methods...
, you may use HSTACK since it will generally join columns (in this case the two tables) as a whole without converting said values into strings.
You may use:
= LET(
table1, A2:B6, table2, E2:F4,
WRAPROWS(
TOROW( BYROW( table1, LAMBDA( x,
TOROW( BYROW( table2, LAMBDA( y,
HSTACK( x, y)
) ) )
) ) ),
COLUMNS( table1 ) + COLUMNS( table2 )
)
)
Given the following tables:
Table 1 | (A2:B6) |
---|---|
"Albion" | "Artichoke Soufflé Omelett" |
"Burgess" | "Lemony Braised Chicken" |
"Hamad" | "Mabo Dofu Smoothie" |
"Berengari" | "Chicken-Fried Plantains" |
"Sengupta" | "Smoky Vegan Corn Salad" |
and
Table 2 | (E2:F4) |
---|---|
"Cho" | "flavor" |
"Nikkelson" | "texture" |
"Rodríguez" | "process" |
I got:
Output | |||
---|---|---|---|
"Albion" | "Artichoke Soufflé Omelett" | "Cho" | "flavor" |
"Albion" | "Artichoke Soufflé Omelett" | "Nikkelson" | "texture" |
"Albion" | "Artichoke Soufflé Omelett" | "Rodríguez" | "process" |
"Burgess" | "Lemony Braised Chicken" | "Cho" | "flavor" |
"Burgess" | "Lemony Braised Chicken" | "Nikkelson" | "texture" |
"Burgess" | "Lemony Braised Chicken" | "Rodríguez" | "process" |
"Hamad" | "Mabo Dofu Smoothie" | "Cho" | "flavor" |
"Hamad" | "Mabo Dofu Smoothie" | "Nikkelson" | "texture" |
"Hamad" | "Mabo Dofu Smoothie" | "Rodríguez" | "process" |
"Berengari" | "Chicken-Fried Plantains" | "Cho" | "flavor" |
"Berengari" | "Chicken-Fried Plantains" | "Nikkelson" | "texture" |
"Berengari" | "Chicken-Fried Plantains" | "Rodríguez" | "process" |
"Sengupta" | "Smoky Vegan Corn Salad" | "Cho" | "flavor" |
"Sengupta" | "Smoky Vegan Corn Salad" | "Nikkelson" | "texture" |
"Sengupta" | "Smoky Vegan Corn Salad" | "Rodríguez" | "process" |