Combinations and Permutations

[Scott]

Hi all

Here's what I'm trying to do, and I haven't had much luck with it yet. I've got six items, let's say in A1:A6. Some items may be repeated, but not necessarily. I need a quick way to set up Excel to arrarange the items in all possible groups of 3, 4, 5, and 6. These groups can't repeat an item, but it can use two items that happen to be the same if the case presents itself

It would take awhile to do this by hand..
For the possible groups of 3, there are 6 choices for the first item, 5 choices for the second item, and 4 choices for the third item. 6 x 5 x 4 = 120

For groups of 4: 6 x 5 x 4 x 3 = 36

etc

Any ideas?

 

[Scott]

I figured it out myself

It did take a little time, but wasn't too bad
In A1:A6 I choose as the entry point to put in my items. For now, I choose prime number of 2 digits: 11, 13, 17, 19, 23, and 29

Even though I wanted sets of 3, 4, 5, and 6, I started with 2
B1=$A$1*A1 copy down C1=$A$2*A1 copy down ... G1= $A$6*A

Then I stacked these 6 columns in one in B, and proceeded to do the same thing with sets of

C1=$A$1*B1 (because col B is now the sets of three) ... same as above, then moved them all into column

Repeat this process until all columns are compete. Now I needed to get rid of the entries that contained duplicate items. I put a helper column next to each one I had just made. The helper column tested the entry next to it to make sure it contained no more than one of each of the items. Since I choose to use only prime numbers, the test was pretty simple. Something like this (F1 helper testing E1 column): F1=IF(OR(FLOOR(E1/$A$1/$A$1,1)=$A$1/$A$1,FLOOR(E1/$A$2/$A$2,1)=E1/$A$2/$A$2 ....),"TAKE OUT",E1) For the entries I wanted to remove, the helper column marked them with TAKE OUT, other it just gave the to value. After I put helpers on all the columns, I copied each helper and pasted only the values back, so I could sort it. Sorting allowed me to delete only the entries marked TAKE OUT, and not the others

I did some edit-> replacements to change the * to &, and make it pretty, and voila, there you go

I might have forgot something, but that's the gist of it

Scot

----- Scott wrote: ----

Hi all

Here's what I'm trying to do, and I haven't had much luck with it yet. I've got six items, let's say in A1:A6. Some items may be repeated, but not necessarily. I need a quick way to set up Excel to arrarange the items in all possible groups of 3, 4, 5, and 6. These groups can't repeat an item, but it can use two items that happen to be the same if the case presents itself

It would take awhile to do this by hand..
For the possible groups of 3, there are 6 choices for the first item, 5 choices for the second item, and 4 choices for the third item. 6 x 5 x 4 = 120

For groups of 4: 6 x 5 x 4 x 3 = 36

etc

Any ideas?