Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
all possible combinations. | Excel Discussion (Misc queries) | |||
Combinations | Excel Programming | |||
I know how to concatenate ,can one de-concatenate to split date? | New Users to Excel | |||
Combinations | Excel Worksheet Functions | |||
Combinations | Excel Programming |