Cross product of text fields

[tenneps199]

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

 

[VoG]

Try

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]

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.