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
 

Some videos you may like

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
Joined
Jun 19, 2002
Messages
63,650
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,939
Messages
5,525,741
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top