Is there a way to do cross join or cartesian product in excel?

Question

At the moment I can’t use a typical database, so I’m temporarily using excel. Any ideas?

Answer 1

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

Here's how you make a Cartesian product of 2 dimensions using standard Excel and without VBA:

1) Plot dim1 vertically and dim2 horizontally. Merge dimension elements at intersections:

2) Incurable data. Start the pivot table wizard using ALT-DP (do not hold ALT, press once). Select Multiple Consolidation Ranges → create one page. → Select all cells (including headers!) And add them to the list, click next.

3) Build the resulting values vertically and parse the concatenated strings

Voila, you have a cross. If you need to add another dimension, repeat this algorithm again.

Greetings

Konstantin.

Answer 2

This article helped me cross connect in Excel:

http://www.excelguru.ca/blog/2016/05/11/cartesian-product-joins-for-the-excel-person/

This requires Microsoft Add Microsoft Power Query for Excel https://www.microsoft.com/en-us/download/details.aspx?id=39379

Answer 3

Using VBA, you can. Here is a small example:

Sub SqlSelectExample() 'list elements in col C not present in col B Dim con As ADODB.Connection Dim rs As ADODB.Recordset Set con = New ADODB.Connection con.Open "Driver={Microsoft Excel Driver (*.xls)};" & _ "DriverId=790;" & _ "Dbq=" & ThisWorkbook.FullName & ";" & _ "DefaultDir=" & ThisWorkbook.FullName & ";ReadOnly=False;" Set rs = New ADODB.Recordset rs.Open "select ccc.test3 from [Sheet1$] ccc left join [Sheet1$] bbb on ccc.test3 = bbb.test2 where bbb.test2 is null ", _ con, adOpenStatic, adLockOptimistic Range("g10").CopyFromRecordset rs '-> returns values without match rs.MoveLast Debug.Print rs.RecordCount 'get the # records rs.Close Set rs = Nothing Set con = Nothing End Sub 

Answer 4

Try using CROSS JOIN . More on MSDN

You can use the expression CROSSJOIN(table1, table2) to create a Cartesian product.