Is there a way to perform a cross join or Cartesian product in excel?

sql database excel join cross-join
user1248831 picture user1248831 · Nov 18, 2014 · Viewed 27.2k times · Source

At the moment, I cannot use a typical database so am using excel temporarily. Any ideas?

The enter image description here

Answer

BusinessAlchemist picture BusinessAlchemist · Nov 5, 2017

You have 3 dimensions here: dim1 (ABC), dim2 (123), dim3 (XYZ).

Here is how you make a cartesian product of 2 dimensions using standard Excel and no VBA:

1) Plot dim1 vertically and dim2 horizontally. Concatenate dimension members on the intersections:

Step 1 - plotting dimensions

2) Unpivoting data. Launch pivot table wizard using ALT-D-P (don't hold ALT, press it once). Pick "Multiple consolidation ranges" --> create a single page.. --> Select all cells (including headers!) and add it to the list, press next.

step2 - unpivoting data

3) Plot the resulting values vertically and disassemble the concatenated strings

step 3 - disassemble strings

Voila, you've got the cross join. If you need another dimension added, repeat this algorithm again.

Cheers,

Constantine.

Related questions

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More