# Cross product of text fields

#### tenneps199

##### New Member
I have 2 columns of data, each with variable length:
C1 C2
a x
b y
c z
d

I need to create a cross-product of these text fields that would provide the following:
ax
ay
az
bx
by
bz
cx
cy
cz
dx
dy
dz

Is there a formula in Excel or a VBA macro that could produce this?
Thanks.
MD

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### VoG

##### Legend
Try

Code:
``````Sub Xprod()
Dim LRA As Long, LRB As Long, i As Long, j As Long, k As Long
LRA = Range("A" & Rows.Count).End(xlUp).Row
LRB = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LRA
For j = 1 To LRB
k = k + 1
Range("C" & k).Value = Range("A" & i).Value & Range("B" & j).Value
Next j
Next i
End Sub``````

#### tusharm

##### MrExcel MVP
Suppose your sample data are in B2:C5.

Then, in some cell, say D2, enter the formula
=INDEX(\$B\$2:\$B\$5,INT((ROW()-ROW(\$D\$2))/ROWS(\$C\$2:\$C\$4))+1)&INDEX(\$C\$2:\$C\$4,MOD(ROW()-ROW(\$D\$2),ROWS(\$C\$2:\$C\$4))+1)

Copy D2 down until you get an error.