Macro posted by Gary's Student.

Sub MixAndMatch()
Dim s1 As Worksheet
Dim s2 As Worksheet
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
i = Cells(Rows.Count, "A").End(xlUp).Row
j = Cells(Rows.Count, "B").End(xlUp).Row
k = 1
For ii = 1 To i
v1 = Cells(ii, 1).Value
For jj = 1 To j
s2.Cells(k, 1).Value = v1 & " " & s1.Cells(jj, 2).Value
k = k + 1
Next
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 13 Oct 2009 12:03:05 -0700, MF wrote:

How do you concatenate 2 large lists? I know how to concatenate straight
across one for one, but I need all of list 1 to concatenate with each zip
code. I can manually type in concatenate A2, B2, then A2,B3...etc. but when I
copy the formula, it changes it back to A2,B2, A2, B3, etc (one to one across)

Example:

red cars 92688
blue cars 92618
orange cars

I want to end up with this:

red cars 92688
blue cars 92688
orange cars 92688
red cars 92618
blue cars 92618
orange cars 92618