Cross product of text fields

tenneps199

New Member
Joined
Jul 18, 2006
Messages
12
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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
Joined
May 28, 2002
Messages
11,007
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.
 

Forum statistics

Threads
1,085,648
Messages
5,384,914
Members
401,926
Latest member
healthguy

Some videos you may like

This Week's Hot Topics

Top