How can I create a "Cross-Join" in excel?

Question

I have 2 sheets in excel, each with 10 rows. Is there some sort of "cross-join" formula/macro in Excel that allows me to create a 3rd sheet with all rows cross-joined (100 rows in total).

Answer 1

If using OLEDB is not an options below function can be used to generate cross product

Option Base 1

Function Cross_Product_range(r1 As Range, r2 As Range) As Variant
    Cross_Product_range = Cross_Product_array(r1.Value, r2.Value)
End Function

Function Cross_Product_array(a1 As Variant, a2 As Variant) As Variant
    On Error GoTo ErrorHandler
    Dim TempArray(), k, m
    ReDim TempArray(UBound(a1) * UBound(a2), UBound(a1, 2) + UBound(a2, 2))
    k = 1
    For i = 1 To UBound(a1)
        For j = 1 To UBound(a2)
            m = 1
            For u = 1 To UBound(a1, 2)
                TempArray(k, m) = a1(i, u)
                m = m + 1
            Next
            For u = 1 To UBound(a2, 2)
                TempArray(k, m) = a2(j, u)
                m = m + 1
            Next
            k = k + 1
        Next
    Next
    Cross_Product_array = TempArray
    Exit Function
    ErrorHandler:
    Debug.Print Err
End Function

Once above function is added to vba module, To produce cross product of A1:B5 and C1:D5 select target cells (say E1:H25) and enter formula and use ctrl + shift + enter

=Cross_Product_range(A1:B5,C1:D5)

Answer 2

You could do this fairly easily with Power Query (if you have it with your version of Excel):

Set up a data connection to each of tables you want to combine: Select the table in Excel, then choose "From Table/Range" from "Data" on the ribbon. This will bring up the Power Query editor.

To get back to Excel, click the little arrow on the "Close and Load" button, select "Close and Load To" and then "Connection Only". Otherwise you will end up with a copy of your table in Excel...

Do the same for the second table.

The "Queries & Connections" pane should now be open on the right of the window. If not, click "Data -> Queries & Connections" from the ribbon.

The two tables should be listed as queries.

The next step is to make a cross-join query combining the two and return the result to Excel:

Right-click on one of the queries in the "Queries & Connections" pane and select "Reference". This will open the Power Query editor again and create a third query which is pointing at one of your tables.

Now choose "Add Column -> Custom Column" from the Power Query ribbon... The formula for the new column will be the name of the second query - ie the data to be cross-joined. If there is no space in the name, you can type it directly, (e.g. = Table2, otherwise it will need to be = #"Table 2".

You should now have a new column labelled "Custom" with "Table" in each cell. Click the little symbol in the column heading to expand each table.

All going well, you're done! Click "Close and Load" and the cross-joined table should appear in a new worksheet.

One thing to note: this cross-join will not update automatically when you change the source data. You will need to right-click and select "Refresh" to re-run the cross-join query.

It's probably more straightforward than I am making it sound!