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

--
Thanks :-)

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



Try this:

=IF(ROW(1:1)<=COUNTA(A:A)*COUNTA(B:B),
INDIRECT(ADDRESS(MOD(ROW(1:1)-1,COUNTA(A:A)) +2 ,1))&" "&
INDIRECT(ADDRESS(INT((ROW(1:1)-1)/COUNTA(A:A)) +2 ,2)),"")

"+2" should be adjusted (twice) to match your starting row.

Glenn wrote:
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



Try this:

=IF(ROW(1:1)<=COUNTA(A:A)*COUNTA(B:B),
INDIRECT(ADDRESS(MOD(ROW(1:1)-1,COUNTA(A:A)) +2 ,1))&" "&
INDIRECT(ADDRESS(INT((ROW(1:1)-1)/COUNTA(A:A)) +2 ,2)),"")

"+2" should be adjusted (twice) to match your starting row.

By "starting row", I mean where your two lists start, not where the concatenated
results are. Also, I am assuming no blank entries in the middle of either list.

Hi,

1. Say your data is in range C5:D7. In C4, type Cars and in D4, type
Numbers
2. Select C4:C7 and assign it a name, say dummy1
3. Select D4:D7 and assign it a name, say dummy2
4. Now save the file
5. Click on any blank cell
6. Go to Data Import External Data New Database Query
7. Select Excel files
8. Select dummy1 and click on the greater than symbol to get the car types
on the right
9. Select dummy2 and click on the greater than symbol to get the numbers on
the right
10. Click on Next and then click on OK
11. Microsoft Query will open up and you will see the desired result
12. Go to File Return Data to MS Office Excel
13. Choose the cell where you want the output.

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"MF" wrote in message
...
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

--
Thanks :-)

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