excelexcel-formulacartesian-productcross-join

How to get the cartesian product (cross join) of two sets via Excel Formulas


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

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

Solution

  • Without using LAMBDA() helper function:

    enter image description here


    =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)