Merging Text Help

[MarkyD]

Hello,

I need to take a list of words and merge them with another list of words. For example:

First List:
Oshkosh,Appleton,Menasha,Neenah,Green Bay
Second List:
Tile Setter,Tile Installer,Ceramic Tile Installer,Etc. Etc.


I want to make a CSV file that comes up with these results from the above example:

Oshkosh Tile Setter,Oshkosh Tile Installer,Oshkosh Ceramic Tile Installer,Appleton Tile Setter,Appleton Tile Installer, etc. etc.

Is there a way to do this with Excel automatically? You can imagine the amount of results with Very large lists. This would save time, trying to type each and every result.

Please help - and if possible give me the term that is used to describe this function - i.e. Word Merging.

Regards,

MarkyD

[DOR]

Put the city list in column A of sheet1 and the Occupation list in
column B. In sheet2 cell A1 put

=Sheet1!$A1&" "&INDEX(Sheet1!$B:$B,COLUMN())

Copy across to include all occupations and and down to include all
cities.

Save sheet2 as a CSV file.

[MarkyD]

Thank you - Works Wonders. I wish I knew how you came up with the formula.

Mark

[duane]

try this - note you need to name some ranges - it also parses the input lists so it will move data around

if you need to keep original lists i suggest add a copy command to another location at the start

Sub macro1()
'list1 and list2 are names of ranges (cells) with the two lists
' named range "output" is destination cell
' first parse both lists
Dim list1(100) As String
Dim list2(100) As String
List1row = Range("list1").Row
list1col = Range("list1").Column
List2row = Range("list2").Row
list2col = Range("list2").Column
Range("list1").Select
Selection.TextToColumns Destination:=Range("list1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Comma:=True
Range("list2").Select
Selection.TextToColumns Destination:=Range("list2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Comma:=True
' define length of two lists
list1len = Range("list1").End(xlToRight).Column - _
Range("list1").Column
list2len = Range("list2").End(xlToRight).Column - _
Range("list2").Column
For i = 1 To list1len
list1(i) = Cells(List1row, list1col + i - 1).Value
Next i
For i = 1 To list2len
list2(i) = Cells(List2row, list2col + i - 1).Value
Next i
output = ""
For i = 1 To list1len + 1
For j = 1 To list2len + 1
output = output & list1(i) & list2(j) & ", "
Next j
Next i
Range("output").Value = output
End Sub

[DOR]

You're welcome.

To understand formulas like this, look up a few things in Excel help:

1. Enter "cell references" in the answer wizard and then click on "The
difference between abslute and relative cell references" on the right
hand side.

2. Do the same for "column function" and read about that.

3. Then read about "index function".

4. Finally, read about "concatenate" (the & joins strings together -
that's called "concatenating".