List all combinations after selecting limited items from groups.

[reddifreddi]

Hi, I would appreciate some help with this problem as I do not have the skill to work it out. I have searched the internet thoroughly for a solution but have not found one. If it does exist and I came across it, I did not understand it. I use Excel 2000 and 2002.

I would like Excel to list all the combinations under these conditions.

For example, I have four groups with different items in each of them

Group1 1 2 3 4 5 6 7

Group2 8 9 10 11 12

Group3 13 14

Group4 15 16 17

Now, I would like to select say two items from Group1, one item from Group2 one item from Group3 and 2 items from Group4. When the selection is made Excel should list all the combinations.

I found the file: 517306 - Fantasy Recruiting.xls by shg by searching this forum. The file was offered in response to this thread http://www.mrexcel.com/forum/excel-questions/517306-list-combinations-parameters.html. I thought it would give me the information I needed even though the maximum number condition in the file is not relevant but when I attempted to change the number of items selected from what was predetermined, my system hung or ran out of rows or showed the permutations. This file enables the selection of two items. then four items and then another four items.

Also, when I attempted to insert a column in the same file, as I thought was suggested in the instructions, that did not work either.

I also found the combination permutation.xls file by Myrna Larson but that only provides information for one group and not two or more. This may be of use but I do not know how to change it.

Anyway, I hope my request is clear and that someone will assist me with a solution. Please?

 

[mirabeau]

...
...
etc.

try this

Sub group_combos()

'2 from Group1, 1 from Group2, 1 from Group3, 2 from Group4,

Dim a() As String, x As String, y As String
Dim Group1, Group2, Group3, Group4
Dim s1, s2, s3, s4
Dim i1&, i2&, j1&, k1&, l1&, l2&, q&

Group1 = "1 2 3 4 5 6 7"
Group2 = "8 9 10 11 12"
Group3 = "13 14"
Group4 = "15 16 17"

ReDim a(1 To 10 ^ 6, 1 To 1)
x = " ": y = String(4, x)

s1 = Split(Group1, x)
s2 = Split(Group2, x)
s3 = Split(Group3, x)
s4 = Split(Group4, x)

For i1 = 0 To UBound(s1)
    For i2 = i1 + 1 To UBound(s1)
        For j1 = 0 To UBound(s2)
            For k1 = 0 To UBound(s3)
                For l1 = 0 To UBound(s4)
                    For l2 = l1 + 1 To UBound(s4)
                        q = q + 1
                        a(q, 1) = s1(i1) & x & s1(i2) & y & _
                            s2(j1) & y & s3(k1) & y & s4(l1) & x & s4(l2)
                    Next l2
                Next l1
            Next k1
        Next j1
    Next i2
Next i1

Cells(1).Resize(q) = a
Cells(3) = q & " combinations"

End Sub

 

[reddifreddi]

Hi, Ooh, this is very exciting. I have tried it. It works for the example. Is it difficult to cause the code to refer to cells in a worksheet where it would find its data for each group and the number of items to select?

 

[reddifreddi]

Hi Mirabeau, I have studied the code you wrote and although I do not understand it, I have been able to amend it for varying situations and use it. Thank you very much, I am very grateful.

If there is anyone out there that could explain how the code works I would appreciate that also.