Concatenate

  • Thread starter Thread starter MF
  • Start date Start date
M

MF

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
 
MF said:
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
Click to expand...


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 said:
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.
Click to expand...

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
 
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
 
Back
Top