# 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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### 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.

Replies
6
Views
75
Replies
1
Views
274
Replies
10
Views
635
Replies
3
Views
58
Replies
16
Views
3K