[Excel] Cartesian product

Question

Hello,  How can I obtain a Cartesian product of two tables or columns in Excel? For example:  TABLE 1 a b c   TABLE 2 1 2    RESULT a 1 a 2 b 1 b 2 c 1 c 2  Thank you for your help

Raymond PENTIER · Answer

I'm not sure if you want a concatenation (to join the two values "a" and "1" to get "a1") or a multiplication (= value of a x 1).
In the second case, you need to use the matrix function PRODUCTMAT(matrix1;matrix2).
In the first case, I don't have the solution.
--
Retirement is nice! Especially in the Caribbean... :-)
Raymond

aldus · Answer

With a 6-year delay! But the issue remains relevant!

Here’s how I proceed:

I number each item in the two tables (from 1 to 3 for the first table and from 1 to 2 for the second). The pair of values being sought, like (c,2) or c2, will have coordinates (3,2); it will be obtained by decoding the coordinates with two VLOOKUP(...) or two INDEX(...), one for the first coordinate and the other for the second coordinate.

Now, it's just a matter of obtaining the different possible coordinates. This can be easily done by taking all the numbers from 1 to n*m where n is the size of the first table and m is the size of the second table (here from 1 to 3*2, which is from 1 to 6). This is obtained using the following formula:

Calculating the Cartesian product of two tables

Calculating the Cartesian product of two tables Table A Table B 1 a 1 1 2 b 2 2 3 c Size of table A: 3 Size of table B: 2 Coord. 1 Coord. 2 Table AxB 1 =MOD($A13-1;$C$9)+1 =INT(($A13-1)/$C$9)+1 =INDEX($B$5:$B$7;$B13)&INDEX($D$5:$D$6;$C13) 2 =MOD($A14-1;$C$9)+1 =INT(($A14-1)/$C$9)+1 =INDEX($B$5:$B$7;$B14)&INDEX($D$5:$D$6;$C14) 3 =MOD($A15-1;$C$9)+1 =INT(($A15-1)/$C$9)+1 =INDEX($B$5:$B$7;$B15)&INDEX($D$5:$D$6;$C15) 4 =MOD($A16-1;$C$9)+1 =INT(($A16-1)/$C$9)+1 =INDEX($B$5:$B$7;$B16)&INDEX($D$5:$D$6;$C16) 5 =MOD($A17-1;$C$9)+1 =INT(($A17-1)/$C$9)+1 =INDEX($B$5:$B$7;$B17)&INDEX($D$5:$D$6;$C17) 6 =MOD($A18-1;$C$9)+1 =INT(($A18-1)/$C$9)+1 =INDEX($B$5:$B$7;$B18)&INDEX($D$5:$D$6;$C18)

And here’s the result:

 Calculating the Cartesian product of two tables Table A Table B 1 a 1 1 2 b 2 2 3 c Size of table A: 3 Size of table B: 2 No. Coord. 1 Coord. 2 Table AxB 1 1 1 a1 2 2 1 b1 3 3 1 c1 4 1 2 a2 5 2 2 b2 6 3 2 c2

Otherwise, there's a more basic way to manually create the Cartesian product table from a matrix:

 1 2 a =$G5&H$4 =$G5&I$4 b =$G6&H$4 =$G6&I$4 c =$G7&H$4 =$G7&I$4

With the result:

Table AxB 1 2 a a1 a2 b b1 b2 c c1 c2

Then all that's left to do is take the columns and stack them to get the desired table!

[Excel] Cartesian product

2 réponses

Canon mb5350 - cartridge replacement impossible

Texture issues in enshrouded

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.

Wake on lan with livebox