Concatenate

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
 
G

Glenn

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


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.
 
G

Glenn

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.

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.
 
A

Ashish Mathur

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
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top