Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
Hope this is the right place for this – I've kinda hit a brick wall
with this one and was hoping that one of you guys could point me in the right direction; I have a 3 columns of words headed up as type, colour, sound The number of words (rows) varies can be 5 can be 20, I add a new set of 3 words by just tagging them at the bottom of the column. No sorting is needed but I need to be able to generate, perhaps on another sheet, a list of all of the possible combinations of those words. So I guess Im saying that I have a matrix that grows and I would like to press a button and see the permutations listed underneath each other on a separate sheet – ideally with a space between each set. I hope this makes some kinda sense <smile Thanks in advance to anyone who can help, kind regards Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
can you give an example? i.e. type1 colour1 sound1 type2 colour2 sound2 would give type1 colour1 sound1 type2 colour2 sound2 type1 colour1 sound1 type1 colour1 sound2 type1 colour2 sound1 type1 colour2 sound2 type2 colour1 sound1 type2 colour1 sound2 type2 colour2 sound1 type2 colour2 sound -- schuurke2 ----------------------------------------------------------------------- schuurke28's Profile: http://www.excelforum.com/member.php...fo&userid=1581 View this thread: http://www.excelforum.com/showthread.php?threadid=27312 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell1 as Range, cell2 as Range, cell3 as Range Dim rw as Long With Worksheets("Sheet1") set rng1 = .Range(.cells(1,1),.cells(1,1).End(xldown)) set rng2 = .Range(.cells(1,2),.cells(1,2).end(xldown)) set rng3 = .Range(.cells(1,3),.cells(1,3).End(xldown)) End With rw = 0 with Worksheets("Sheet2") for each cell1 in rng1 for each cell2 in rng2 for each cell3 in rng 3 rw = rw + 1 .Cells(rw,1).Value = cell1 & " " & cell2 & " " & cell3 Next Next Next End With This does no checking for going beyond the end of the worksheet, so you would need to add type of code if your lists get big enough to cause that. -- Regards, Tom Ogilvy "chipper" wrote in message om... Hope this is the right place for this - I've kinda hit a brick wall with this one and was hoping that one of you guys could point me in the right direction; I have a 3 columns of words headed up as type, colour, sound The number of words (rows) varies can be 5 can be 20, I add a new set of 3 words by just tagging them at the bottom of the column. No sorting is needed but I need to be able to generate, perhaps on another sheet, a list of all of the possible combinations of those words. So I guess Im saying that I have a matrix that grows and I would like to press a button and see the permutations listed underneath each other on a separate sheet - ideally with a space between each set. I hope this makes some kinda sense <smile Thanks in advance to anyone who can help, kind regards Michael |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
thank you so much for helping - this is perfect. just one last point
ideally i would like the generated content to appear on sheet2 starting at row 5 and for each value to be in its own cell. any pointers would be greatly appreciated kind regards Michael |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
Dim rng1 as Range, rng2 as Range, rng3 as Range
Dim cell1 as Range, cell2 as Range, cell3 as Range Dim rw as Long With Worksheets("Sheet1") set rng1 = .Range(.cells(1,1),.cells(1,1).End(xldown)) set rng2 = .Range(.cells(1,2),.cells(1,2).end(xldown)) set rng3 = .Range(.cells(1,3),.cells(1,3).End(xldown)) End With rw = 4 with Worksheets("Sheet2") for each cell1 in rng1 for each cell2 in rng2 for each cell3 in rng 3 rw = rw + 1 .Cells(rw,1).Value = cell1 .Cells(rw,2).Value = cell2 .Cells(rw,3).Value = cell3 Next Next Next End With -- Regards, Tom Ogilvy "chipper" wrote in message om... thank you so much for helping - this is perfect. just one last point ideally i would like the generated content to appear on sheet2 starting at row 5 and for each value to be in its own cell. any pointers would be greatly appreciated kind regards Michael |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
a problem in generating a list of all of the possible combinations of those words
just a quick line to Tom - saying thanks. what you did really helped
kind regards Michael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating a list of all possible number/letter combinations | Excel Programming | |||
Generating Possible Combinations and Assigning ID Number | Excel Worksheet Functions | |||
Problem generating list | Excel Discussion (Misc queries) | |||
Generating excel combinations | Excel Discussion (Misc queries) | |||
Function generating all possible combinations of set of numbers | Excel Worksheet Functions |