List combinations with parameters?

[julios]

Hi all. I am new both here and in excel. I did a quick search, but couldn't find what i was looking for (possibly cause i wouldn't recognize it even if i saw it). I have a problem and i need to ask if it is possible to solve through excel.

I have 3 categories of numbers say in 3 columns of a worksheet. For example category A has the numbers 73, 31, 55, 68. Category B 59, 57, 21, 35, 72, 65, 44, 58, 32, 33 and category C 72, 21, 37, 48, 68, 70, 49, 26, 41, 85.

I need to make combinations of 10, yes i know they would be a lot, so i want to include some parameters that will make the results fewer.

For example every 10-numbered combination should get only 2 numbers of category A, 4 numbers of category B and another 4 from category C.

The sum of each combination when the numbers are added should be no more than 500.

Is it possible for excel to generate and display all possible combinations given the above parameters??

If there is a solution and since I'm a newbie, please explain as if to an ape.

Thank you very much in advance.

 

[julios]

adding pic

i am currently getting 1.961.256 possible combinations, but i need to run this number down via the parameters and have the possible combinations generated and displayed.

 

[julios]

well? nobody?

 

[xenou]

i am currently getting 1.961.256 possible combinations

Has it escaped your notice that these many numbers exceed the number of rows in Excel 2007? I cannot see any possible reason to have such a list. What is your ultimate aim here? Normally, permutational and combinatorial "problems" can be "answered" mathematically, and do not require the physical listing of millions of numbers.

 

[shg]

For example every 10-numbered combination should get only 2 numbers of category A, 4 numbers of category B and another 4 from category C.

So the number of arrangements is

=combin(4,2)*combin(10,4)*combin(10,4) ~ 264K

... and the constraint that the total be <= 500 reduces it further.

Even so, what's the value in listing them?

 

[julios]

Has it escaped your notice that these many numbers exceed the number of rows in Excel 2007? I cannot see any possible reason to have such a list. What is your ultimate aim here? Normally, permutational and combinatorial "problems" can be "answered" mathematically, and do not require the physical listing of millions of numbers.

Actually I want to narrow the number down as far as possible, thus i need to use the above parameters. I don't want it to display 2mil combinations.

So the number of arrangements is

=combin(4,2)*combin(10,4)*combin(10,4) ~ 264K

... and the constraint that the total be <= 500 reduces it further.

Even so, what's the value in listing them?

The question was triggered out of curiosity and a discussion with a mathematician cousin. Probably any resulting number of combinations should have no practical value.

So is it possible to narrow the number further down with the <= 500 constraint? And if so, how? Further more will excel display all possible combinations?

For testing purpose and in order to produce a number of combinations that would fit in excel, I could use less numbers in each category: say 3 in the first and 6 in the other two categories.

 

[diddi]

if you are that desperate to see every combination, then dump it to a text file. now there is no excel sheet size restriction and you can have millions of results with ease.

 

[xenou]

The question was triggered out of curiosity and a discussion with a mathematician cousin. Probably any resulting number of combinations should have no practical value.

Curiousity about what? How to compute the number of combinations? The effect of the constraint? The means by which Excel might do this? Your answer is vague.

So is it possible to narrow the number further down with the <= 500 constraint? And if so, how? Further more will excel display all possible combinations?

I have seen (and occasionally explored) solutions using VBA. Excel as such has no inbuilt methods for displaying combinations. So if your hope was some simple formula to use, no luck. It's generally ridiculous to try to do this for results with millions of combinations as actual output, though 264,000 is in the realm of possibility. It would require (I guess) a small program to crunch the numbers - not sure.

 

[xenou]

not sure where you are actually headed with this. Here are the combinations. The first 109,665 values sum to 500 or less. The remaining values sum above 500 for a total of 264600 combinations. Since at least one value (68) repeats in your Sets A and C, these are considered separate values for the purpose of combinations, even though they are the same number. You will need Excel 2007 or 2010 to view the results in Excel, but the txt file can be opened with notepad or any text editor.
<a href="http://northernocean.net/etc/mrexcel/20101225_Combinations.zip">Combinations</a>
(md5 hash, zip file: 9d8446c3aa58d0f35f2c743b9e9f0790)

The code i used is below (this is my foray into combinatorial algorithms). It only works with 1 digit values so I used A,B,C ... as stand ins for your actual values and then converted the results back to your original values. A workbook with the code and conversion tables is here: <a href="http://northernocean.net/etc/mrexcel/20101225_Combinations2.zip">Sample Workbook</a>
(md5 hash, zip file: fa806d5bf15c5f1fc4ace072ba7e9a76)


You can also find some good examples of code from posts by pgc01 (among others).

Option Explicit

Private a() As String, b() As String, c() As String, d(0 To 264599) As String

Sub F00()
Call My_Combin_1
Call My_Combin_2
Call My_Combin_3
Call My_Combin_4
End Sub

Sub My_Combin_1()
Dim x() As String, n As Byte, r As Byte, s As String, i As Long
s = "ABCD"
n = Len(s)
r = 2
x = My_Combin(s, n, r)
For i = 0 To UBound(x)
    a = x
Next i

End Sub

Sub My_Combin_2()
Dim x() As String, n As Byte, r As Byte, s As String, i As Long
s = "EFGHIJKLMN"
n = Len(s)
r = 4
x = My_Combin(s, n, r)
For i = 0 To UBound(x)
    b = x
Next i

End Sub

Sub My_Combin_3()
Dim x() As String, n As Byte, r As Byte, s As String, i As Long
s = "OPQRSTUVWX"
n = Len(s)
r = 4
x = My_Combin(s, n, r)
For i = 0 To UBound(x)
    c = x
Next i

End Sub

Sub My_Combin_4()
Dim i As Long, j As Long, k As Long, l As Long
    For i = 0 To UBound(a)
        For j = 0 To UBound(b)
            For k = 0 To UBound(c)
                d(l) = a(i) & b(j) & c(k)
                Sheet1.Cells(l + 8, 1).Value = d(l)
                l = l + 1
            Next k
        Next j
    Next i
End Sub

Function My_Combin(ByVal s As String, n As Byte, r As Byte) As String()
'//s must be sorted descending/single digit elements
Dim a() As String, b() As String, args() As Variant
Dim i As Long, j As Long, k As Long

If r = 0 Or n = 0 Or r > n Then
    
    ReDim Preserve a(0 To i)
    a(i) = ""
    i = i + 1
    Debug.Print "invalid call: r=" & r & " n=" & n & " s=" & s

ElseIf n = r Then
    
    ReDim Preserve a(0 To i)
    a(i) = s
    i = i + 1

ElseIf r = 1 Then
    
    For j = 1 To Len(s)
        ReDim Preserve a(0 To i)
        a(i) = Mid(s, j, 1)
        i = i + 1
    Next j
    
ElseIf r < n Then

    ReDim args(0 To n - r + 1)
    For j = 1 To (Len(s) - r + 1)
        args(0) = Mid(s, j, 1)
        args(1) = Mid(s, j + 1, Len(s) - j)
        args(2) = r - 1
        
        b = My_Combin(args(1), Len(args(1)), CByte(args(2)))
        For k = 0 To UBound(b)
            ReDim Preserve a(0 To i)
            a(i) = args(0) & b(k)
            i = i + 1
        Next k
    Next j
    
End If

My_Combin = a

End Function

 

[julios]

if you are that desperate to see every combination, then dump it to a text file. now there is no excel sheet size restriction and you can have millions of results with ease.

I don't quite understand what you mean by "dump it to a text file". I need a program that will generate the combinations. And i need <1000 results.

Curiousity about what? How to compute the number of combinations? The effect of the constraint? The means by which Excel might do this? Your answer is vague.

Curiosity about how to generate all possible combinations through excel or another program. The subject was triggered by an online fantasy sports game where you have to pick players with a budget. The vast number of the results prohibits actually using such a code, but as i said...curiosity...

I have seen (and occasionally explored) solutions using VBA. Excel as such has no inbuilt methods for displaying combinations. So if your hope was some simple formula to use, no luck. It's generally ridiculous to try to do this for results with millions of combinations as actual output, though 264,000 is in the realm of possibility. It would require (I guess) a small program to crunch the numbers - not sure.

VBA??

Of course I am not crazy enough to want 264k, let alone millions of combinations displayed. I was just thinking that if i lessen the amount of numbers in each category and use the <=500 constraint I could end up with less than 1000 combinations.

In any case thank you very much for the time you have given this. I will now try to get around all the facts

If i understand correctly, you didn't use excel to generate the combinations. There is no actual formula in excel to do this, is there? (i mean i can't just tip in new numbers in your file and get other results?)