Total Possible Outcomes!

[Mike]

This one has got me stumped, and I am pretty good normally with Excel!

There are 5 catagory's going horiz: [GARMENT, MANUFACTORER, SIZE, COLOUR
1, COLOUR 2]

6 rows of data under each catagory, e.g [GARMENT = shirt, shorts, polo,
socks, jacket, vest] [ SIZE = XS, SM, MD, LG, XL, XX] etc etc

I need to return a list all the possible outcomes from the rows of data.
There must be a way of excel doing this for you? Anyone have any clues?

Thanks for you time

Mike



** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Dave Hawley]

Hi Mike

Check out the COMBIN and PERMUT Functions.

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Mike]

Thanks for the reply. I did find these, but they seem only tell me the
"number" of possible outcomes, not the "actual" outcomes.

It refers to a lottery, which is a good analogy. Permut will tell you
the number of possible outcomes, but I need to result each and every
lottery number.

Using the lottery analogy, it may be a little more easier to think of a
solution!

Anyone else have any ideas?

Thanks

Mike

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Otto Moehrbach]

Mike
I think that you want a list of all possible COMBINATIONS rather than a
list of all possible PERMUTATIONS.
IN COMBINATIONS, abcde is the same as edcba. In PERMUTATIONS, they are
not the same.
The COMBIN function for 30 items taken 5 at a time results in a list
that is 142,506 long. Is that what you want? HTH Otto

 

[sumit goyal]

Hi MIke

If your data is in cells from A1 to D7
with option in from row no 2 to 7.
Copy G1 to G7776 =INDIRECT("A"&MOD(ROUNDUP(ROW()/(6*6*6*6),),6)+2)
Copy H1 to H7776 =INDIRECT("B"&MOD(ROUNDUP(ROW()/(6*6*6),),6)+2)
Copy I1 to I7776 =INDIRECT("C"&MOD(ROUNDUP(ROW()/(6*6),),6)+2)
Copy J1 to J7776 =INDIRECT("D"&MOD(ROUNDUP(ROW()/6,),6)+2)
Copy K1 to K7776 =INDIRECT("E"&MOD(ROUNDUP(ROW(),),6)+2)

Thanks
Sumit

 

[Otto Moehrbach]

Mike
I have to correct myself. I think the list you want is generated by the
following macro. Change the column and row for the start of the list ("f"
in this macro) to suit your data layout.
The list is 7776 long and takes 0:1:16 (one minute, 16 seconds) to
generate on my machine (P3, 1 gig, with .75 gig of memory). Is this what
you want?
I am sending you a small file direct with everything setup as you
described. Just click on the button and wait. HTH Otto
Sub ListAllPossibleOutcomes()
Application.ScreenUpdating = False
Dim a As Range, b As Range, c As Range, d As Range, e As Range
Dim f As Range 'Destination cell in Column A
Set f = [G1]
[A10] = Now
For Each a In Range("Garment")
For Each b In Range("Mfr")
For Each c In Range("Size")
For Each d In Range("Colour1")
For Each e In Range("Colour2")
a.Copy f
b.Copy f(, 2)
c.Copy f(, 3)
d.Copy f(, 4)
e.Copy f(, 5)
Set f = f(2)
Next e
Next d
Next c
Next b
Next a
[A11] = Now
Application.ScreenUpdating = True
End Sub

 

[Mike]

Excel(lent) no pun intended!

Thanks Sumit, that does the trick. Now a little bit of cocantenating,
and that will do the trick!

The actual data that I am processing is a lot bigger than the sample! So
this will save a lot of time.

Thanks again everyone who replied.

Cheers

Mike

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Mike]

Thanks Also to Otto. The macro works a treat!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***