# Cross Join Columns in Excel

03 Jul 2014 - about 1 min to readAs part of a project I’m working on I need to create a cross join of users against symbols; the idea being that any of the given users might look at any of the given symbols. I have a list of users and a list of symbols which I essentially need a cross-join on.

For this example I’ll use cars and engine/transmission variants.

From these two lists I need to quickly create all possible combinations;

I can’t find any function that will do what I was so I’ve created a VBA function that will do it;

```
Function CrossJoin(r1 As Range, r2 As Range) As Variant
Dim size As Integer
Dim arr As Variant
Dim offset As Integer
size = r1.Rows.Count * r2.Rows.Count
ReDim arr(0 To size, 0 To 1)
offset = 0
For i = 1 To r1.Rows.Count
For j = 1 To r2.Rows.Count
arr(offset, 0) = r1.Cells(i, 1).Value2
arr(offset, 1) = r2.Cells(j, 1).Value2
offset = offset + 1
Next j
Next i
CrossJoin = arr
End Function
```

To use the function select and empty cell and insert `=CROSSJOIN(A1:A4, B1:B4)`

(adjusting the input range as required) then when the function has evaluated extend the range to the size of the expected cross join. (in this case 16x2).

To calculate the array function hit `F2`

then press `Ctrl+Shift+Enter`

to calculate the array.