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?

 

[reddifreddi]

Any replies, please?

 

[Fazza]

hi

Creating a query using Cartesian join on the data lists is a non-formula & non-code solution.

For example. Set up a header and under it the values you want to use. Give the header & data a normal defined name List1. Similarly for List2, List3 & List4. Save the file. Start the query wizard ALT-D-D-N, then Excel files, OK, browse & select the workbook, OK, see the defined names listed. Successively 'Add' each one then close the 'add tables' dialog box. Go across the GUI successively selecting the header name from List1, List2, List3 & List4. Hit the 'open door' icon to return the results to a worksheet. This is a refreshable query table.

HTH

 

[reddifreddi]

Hi, Thanks for your reply. I have tried to follow your instructions and muddled my way to get some output but it is not what I needed so I do not know if I did it correctly. After I successively "add"ed each list I was not given the option to close the dialog box but only "next" or "cancel" so I chose "next". At that point a dialog box opened stating that MS Query could not continue because it could not join the tables and that I needed to join them manually by dragging the fields. After selecting "OK" a MS Query screen appeared that contained the groups and their numbers. The query appeared to contain all the combinations. When I closed that screen a dialog box appeared stating "returning external data to Microsoft Excel" so I continued with that and it created a list with all the combinations. Is that what was supposed to happen? I need to be able to tell it to select say two items from group one rather than have it give me a list containing every combination. I would appreciate any further assistance.

 

[Fazza]

I must apologise. My instructions were from memory and obviously incorrect. However you've done well and done the right thing regardless of my poor instructions.

I'm not sure how functionally you want to do further steps. If you want, say, two items only from group 1 then just make the list have two items only. [And if necessary redefine the named range to suit the amended range.] Then refresh the query - which happens like a pivot table. So, pick a cell in the query and then right click & choose data refresh. Or from the data menu, choose refresh. Etc.

If you want a different means to control the selections - that is of two/whatever only - then I don't know what you want. There are many ways, of course. The solution I gave was to demonstrate the basic technique - using a query. There is plenty of scope to develop the basic idea - and it is a simple & powerful solution - into something fancier. OK?

 

[reddifreddi]

Hi Fazza

Thanks for clarifying it. I am grateful for your assistance and was pleased to learn something new. Your solution does help although it is not exactly what I need.

For the purpose of clarifying my initial request - I want Excel to give me all the combinations automatically by applying the example conditions which were selecting 2 items from Group1, 1 item from Group2, 1 item from Group3 and 2 items from Group4. I want to see all the combinations in one list. For example they may look like this:

1,2,8,13,15,16
2,6,10,14,16,17
3,7,11,13,15,17
4,6,9,14,15,16
4,6,9,14,16,17
4,6,12,14,16,17

Is my request clear? Is the query function able to extract the data like this?

 

[Fazza]

It is not clear to me. I think though that what I've provided will help you get to where you want.

BTW, if you want the output a single comma separated list that is an easy edit to the query's SQL. Go into MS Query (you can get there from the menu via a right click from within the query's results) and change it there. Where it currently has something (basically) like this on the first line

SELECT List1, List2, List3, List4

which returns four fields. To make it one single field, edit it to be

SELECT List1 & ', ' & List2 & ', ' & List3 & ', ' & List 4 AS [Your New Output Header Name]

Regarding selecting sub-parts of the list. I don't know what you want or how you want to do it: but as it is only a refinement to what I've posted I will leave that to you. There are many ways.

HTH

 

[reddifreddi]

Thanks again, I will have a look at what you have said.

 

[mirabeau]

...
...
etc.

reddifreddi,

Here's a macro you can try

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, " ")
s2 = Split(Group2, " ")
s3 = Split(Group3, " ")
s4 = Split(Group4, " ")

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

 

[mirabeau]

...
...
etc.

reddifreddi,

Here's a macro you can try

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