I need to get the cartesian product of two "sets" via Excel 365 Formulas (not VBA, not Power Query). For instance, my two sets are the two left tables, and the expected cartesian product is the right table:
Cartesian product of two sets in Excel 365
How to get the cartesian product with one Excel formula?
If possible, I'd like a general answer, to work for any two sets, whatever values they contain, whatever the number of columns is in the two sets.
The two sets:
Column1 | Column2 | Column1 | Column2 | |
---|---|---|---|---|
4500005010 | A | PO start | D | |
4500005011 | B | header merge | E | |
4500005012 | C | PO activate | F |
Without using LAMBDA()
helper function:
=LET(
a, TOROW(DataTwo[Column1]&"|"&DataTwo[Column2]),
b, TOCOL(DataOne[Column1]&"|"&DataOne[Column2]&"|"&a),
TEXTSPLIT(TEXTAFTER("|"&b,"|",SEQUENCE(,4)),"|"))
• Or, Option Two:
=LET(
a, ROWS(DataTwo),
b, ROWS(DataOne),
c, CHOOSEROWS(DataOne,INT((SEQUENCE(a*b)-1)/a)+1),
d, CHOOSEROWS(DataTwo,MOD((SEQUENCE(a*b)-1),a)+1),
HSTACK(c,d))
Change variables at the end to evaluate:
=LET(
a, ROWS(DataTwo),
b, ROWS(DataOne),
c, SEQUENCE(a*b),
d, CHOOSEROWS(DataOne,INT((c-1)/a)+1),
e, CHOOSEROWS(DataTwo,MOD((c-1),a)+1),
f, HSTACK(d,e),
f)