Is there a way to perform a cross join or Cartesian product in excel?
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:
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.
3) Plot the resulting values vertically and disassemble the concatenated strings
Voila, you've got the cross join. If you need another dimension added, repeat this algorithm again.
Cheers,
Constantine.
This article helped me perform a cross join in Excel:
http://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/
It requires the Microsoft Add inMicrosoft Power Query For Excelhttps://www.microsoft.com/en-us/download/details.aspx?id=39379
Here is a very easy way to generate the Cartesian product of an arbitrary number of lists using Pivot tables:
https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/
The example is for two lists, but it works for any number of tables and/or columns.
Before creating the Pivot table, you need to convert your value lists to tables.