Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

How to get the cartesian product 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

How to get the cartesian product with one Excel formula?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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)

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading