Suppose that you have a set of row vectors and want to build a cartesian product of them on Microsoft Excel. You can build it with a single formula with spilled array. It may require newer versions of Excel. Columns of each rows don't have to be the same. Of course you can use VSTACK to build an input on the fly, or use spilled range operator like A1# if possible. If your input data contains commas, you can modify the formula by replacing the comma separator in both &","& and TEXTSPLIT(B,",") with any character that won't appear in your data (for example, using a pipe symbol | or semicolon ;).
=LET(
INPUT, A1:M2,
CARTESIAN_PRODUCT, DROP(REDUCE("",REDUCE(TRANSPOSE(CHOOSEROWS(INPUT,1)), SEQUENCE(ROWS(INPUT)-1,,2),LAMBDA(X,Y, TOCOL(X &","&CHOOSEROWS(INPUT,Y)))),LAMBDA(A,B, VSTACK(A,TEXTSPLIT(B,",")))),1),
FILTER(CARTESIAN_PRODUCT,BYROW(CARTESIAN_PRODUCT="",LAMBDA(X,NOT(OR(X)))))
)
This example is taken from here。
Input:
| ♠ | ♣ | ♥ | ♦ | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 |
(Suppose that ♠ is in cell A1)
Output:
| ♠ | 1 |
|---|---|
| ♠ | 2 |
| ♠ | 3 |
| ♠ | 4 |
| ♠ | 5 |
| ♠ | 6 |
| ♠ | 7 |
| ♠ | 8 |
| ♠ | 9 |
| ♠ | 10 |
| ♠ | 11 |
| ♠ | 12 |
| ♠ | 13 |
| ♣ | 1 |
| ♣ | 2 |
| ♣ | 3 |
| ♣ | 4 |
| ♣ | 5 |
| ♣ | 6 |
| ♣ | 7 |
| ♣ | 8 |
| ♣ | 9 |
| ♣ | 10 |
| ♣ | 11 |
| ♣ | 12 |
| ♣ | 13 |
| ♥ | 1 |
| ♥ | 2 |
| ♥ | 3 |
| ♥ | 4 |
| ♥ | 5 |
| ♥ | 6 |
| ♥ | 7 |
| ♥ | 8 |
| ♥ | 9 |
| ♥ | 10 |
| ♥ | 11 |
| ♥ | 12 |
| ♥ | 13 |
| ♦ | 1 |
| ♦ | 2 |
| ♦ | 3 |
| ♦ | 4 |
| ♦ | 5 |
| ♦ | 6 |
| ♦ | 7 |
| ♦ | 8 |
| ♦ | 9 |
| ♦ | 10 |
| ♦ | 11 |
| ♦ | 12 |
| ♦ | 13 |