a problem in generating a list of all of the possible combinations of those words

  • Thread starter Thread starter chipper
  • Start date Start date
C

chipper

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
 
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
 
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.
 
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
 
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
 
just a quick line to Tom - saying thanks. what you did really helped

kind regards

Michael
 
Back
Top