# Thread: Cross product of text fields Thanks: 0 Likes: 0

1. ## Cross product of text fields

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

2. ## Re: Cross product of text fields

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```

3. ## Re: Cross product of text fields

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.