How to create a Cartesian join (cross join) between two tables in Excel, using Microsoft Query. This connects all items in table 1 with all items in table 2, to list all possible combinations.
In this example, there are two tables -- Raw Materials and Packaging -- and each table is on a separate worksheet.
The Raw Materials table is on the sheet named Materials, and the Packaging table is on the sheet named Packaging.
The third sheet in the workbook is named Combined, and this is where the query results will be stored.
With Microsoft Query, you can create a list that combines each item in one table, with all the items in the other table -- a Cartesian join, also called a cross join. You can read more about the different join types on the Microsoft website: Description of the usage of joins in Microsoft Query
To create the Cartesian (cross) join, you'll use MS Query.
It might take a few seconds, but then the Import Data window will open.
You can add formulas to the table, in a new column. The formulas will automatically adjust if the source tables are changed.
=[@[Raw Materials]] & " " & [@Packaging]
You can update the source tables, and then update the query results table, to show the revised data.
If you change the workbook name, the query will need to be updated, before it will run. To manually update the query:
If the file name or location will change frequently, you can use programming to automatically change the file location in the connection.
Paste the following code -- FixQueryConnection -- into a regular module in the workbook, and then run the code when the workbook opens, by adding a Workbook_Open event.
This code was tested in Excel 2010 (32-bit), and might need to be adjusted for other versions of Excel.
Sub FixQueryConnection() ' Dim strFile As String Dim strPath As String Dim strQry As String Dim strCmd As String Dim strConn As String strPath = ActiveWorkbook.Path & "\" strFile = ActiveWorkbook.Name strQry = "Query from Excel Files" strCmd = "SELECT `Materials$`.`Raw Materials`, `Packaging$`.Packaging " strCmd = strCmd & "FROM `Materials$` `Materials$`, `Packaging$` `Packaging$`" strConn = "ODBC;DSN=Excel Files;DBQ=" & strPath & strFile strConn = strConn & ";DefaultDir=" & strPath strConn = strConn & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;" ' With ActiveWorkbook.Connections(strQry).ODBCConnection .BackgroundQuery = True .CommandText = strCmd .CommandType = xlCmdSql .Connection = strConn .RefreshOnFileOpen = False .SavePassword = False .SourceConnectionFile = "" .SourceDataFile = "" .ServerCredentialsMethod = xlCredentialsMethodIntegrated .AlwaysUseConnectionFile = False End With With ActiveWorkbook.Connections(strQry) .Name = strQry .Description = "" End With ActiveWorkbook.Connections(strQry).Refresh ActiveWorkbook.RefreshAll End Sub '====================================
Put this code into the ThisWorkbook module:
Private Sub Workbook_Open() FixQueryConnection End Sub '====================================
Last updated: April 16, 2021 3:31 PM