Concatenate All Possible Combinations
 

I have a spreadsheet with 2 columns. I would like to concatenate each row in
column A with all rows in column B into another sheet. The columns are
different lengths and are between 50 and 150 rows long.
Example:
Column A Column B
Dog Red
Cat Brown
Sheep Yellow
Horse

In sheet 2
Column A
Dog €“ Red
Dog €“ Brown
Dog €“ Yellow
Cat €“ Red
Cat €“ Brown
Cat €“ Yellow
Sheep €“ Red
Sheep €“ Brown
Sheep €“ Yellow
Horse €“ Red
Horse €“ Brown
Horse - Yellow
Help please. Thank you.

Concatenate All Possible Combinations
 

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
--
Gary''s Student - gsnu200906


"Kevin199" wrote:

I have a spreadsheet with 2 columns. I would like to concatenate each row in
column A with all rows in column B into another sheet. The columns are
different lengths and are between 50 and 150 rows long.
Example:
Column A Column B
Dog Red
Cat Brown
Sheep Yellow
Horse

In sheet 2
Column A
Dog €“ Red
Dog €“ Brown
Dog €“ Yellow
Cat €“ Red
Cat €“ Brown
Cat €“ Yellow
Sheep €“ Red
Sheep €“ Brown
Sheep €“ Yellow
Horse €“ Red
Horse €“ Brown
Horse - Yellow
Help please. Thank you.

Concatenate All Possible Combinations
 

I like that.


Gord

On Mon, 5 Oct 2009 16:37:01 -0700, Gary''s Student
wrote:

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

Concatenate All Possible Combinations
 

Works great thank you very much!!

"Kevin199" wrote:

I have a spreadsheet with 2 columns. I would like to concatenate each row in
column A with all rows in column B into another sheet. The columns are
different lengths and are between 50 and 150 rows long.
Example:
Column A Column B
Dog Red
Cat Brown
Sheep Yellow
Horse

In sheet 2
Column A
Dog €“ Red
Dog €“ Brown
Dog €“ Yellow
Cat €“ Red
Cat €“ Brown
Cat €“ Yellow
Sheep €“ Red
Sheep €“ Brown
Sheep €“ Yellow
Horse €“ Red
Horse €“ Brown
Horse - Yellow
Help please. Thank you.

Concatenate All Possible Combinations
 

How about if I just have 1 Column with numbers:

1
2
3
4

and would like all possible combinations? (12, 21, 13, 31, etc..)
could you please tweak your code to that that?

thanks!!!