Number Combinations

[gavbro]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
What I am looking for is to select between 7 and 15 numbers in total, I want all the possible 6 digit combinations for this.<o></o>
<o> </o>
EG: if I choose 2,9,11,13,15,17&26, it would look something like this<o></o>
<o> </o>
2,9,11,13,15,17<o></o>
2,9,11,13,15,26<o></o>
9,11,13,15,17,26<o></o>
And so on.<o></o>
<o> </o>
If I chose more numbers (10) 1,2,3,4,3,6,7,8,9,10 it would start something like this<o></o>
<o> </o>
1,2,3,4,5,6<o></o>
1,2,3,4,5,7<o></o>
1,2,3,4,5,8<o></o>
1,2,3,4,5,9<o></o>
1,2,3,4,5,10<o></o>
And so on.<o></o>
<o> </o>
Please remenber I would like to be able to secelt between 7 and 15 number and be given all the possible combinations.<o></o>
I would like it to be in one sheet but if that can not be done on as many as it takes.<o></o>
<o>It would be good if I could just type the required number into A1,B1,C1 and so on and they just gave the combinations required.</o>
<o> </o>
Thanks in Advance<o></o>

 

[Oaktree]

Welcome to the board.

If you have 15 numbers, there are 15*14*13*12*11*10 = about 3.6MM six number combinations. Listing *all* of them is going to make a very large file.

What will you be using the list for once it's made? Maybe there's a way to get the end result without calculating all of the combinations...

 

[MickG]

Hi, I think this will give you combinations not permutatiions.
In row (1) insert your string of numbers , say 1 to 10 ( columns (A to J)
Run the code.
Input box appears "SELECT" the total 10 cells in row(1), Click OK.
Input Box 2 appears "ENTER" the desired sub number say "6". Click OK
List appear in cell "A2" on.

Dim Up As Integer, Lp As Integer, TBd As Integer, Bd As Integer
Dim oComp As Integer, S As Integer, Ac As Integer, oAc As Integer
Dim Vu As Range, LgVu As Integer
Dim Bound As Integer, oClear
 Dim MyRay, L As Integer, c As Integer
 oClear = Rows(1)
    ActiveSheet.Cells.ClearContents
        Rows(1) = oClear
 
  On Error Resume Next
 Set Vu = Application.InputBox( _
    Prompt:="Please Select Total Range String ", _
     Title:="String Combinations", Type:=8)

If Vu Is Nothing Then
    MsgBox "No Value Entered"
        Exit Sub
End If

On Error Resume Next
  LgVu = Application.InputBox( _
    Prompt:="Please Enter Lenght of Sub-String ", _
     Title:="String Combination length") ', Type:=1


If LgVu <= 0 Then
    MsgBox "No Value Entered"
     Exit Sub
End If

MyRay = Vu.Value
L = Vu.Columns.Count
    Bound = L - (LgVu - 1)

For Lp = 1 To Bound
    Bd = Bd + Lp
        Next Lp
MsgBox "Number of Rows in Combination  " & Bd

c = 1
For oComp = 0 To Bound

For S = oComp To Bound - 1
    c = c + 1
    For Ac = 1 To LgVu
        If Ac = LgVu Then
             oAc = Ac + S
           Else
              oAc = Ac + oComp
            End If
Cells(c, Ac).Value = MyRay(1, oAc)
        Next Ac
    Next S
Next oComp

Hope this helps
Regards Mick

 

[gavbro]

Thanks for the replies,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
MikeG, does not give all possibilities.<o></o>
Eg: 1,3,4,5,6,7 is missing and lots more.<o></o>
<o> </o>
Oaktree, what I am trying to is this, in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Australia</st1lace></st1:country-region> for their lottery they have systems.<o></o>
You can choose 7, 8, 9 to 15 numbers on one line, this gives a better chance of winning.<o></o>
This obviously costs more money to put on however, you can save money if you get all the possible combinations and fill all the tickets out by hand.<o></o>
<o> </o>
So this can increase the chances of winning while saving money at the same time.<o></o>
<o> </o>
Would be great if you can help.<o></o>
<o> </o>
Gavin<o></o>

 

[GlennUK]

Use Sheet1 to input the number of items to use per combination, which is 6 in your case, in cell A1, and enter your numbers to choose from in column B starting at B1. The program uses how ever many numbers you've put in column B to generate the results, so 6 from 8 would be 28 different combinations, and 6 from 15 would be 5005 combinations. The output will be written to Sheet2.

Once you've got this code in a module, and you've set up the inputs in Sheet1, with Sheet1 active just run macro comb.

Sub comb()
    choice = [a1]:     arraycount = 0:    outputpointer = 0
    Dim fromarray() As Long, outputarray() As Long
    
    Sheets("Sheet2").UsedRange.Cells.ClearContents ' ### clear the output area
    
    ' first, find out what the values are that combinations are to be made from
    For Each c In ActiveSheet.Range("B1", ActiveSheet.Range("B1").End(xlDown))
        If Not IsEmpty(c) Then
            arraycount = arraycount + 1
            ReDim Preserve fromarray(arraycount)
            fromarray(arraycount) = c.Value
        End If
    Next
    
    numcomb = Application.WorksheetFunction.Combin(arraycount, choice)
    ReDim outputarray(numcomb, choice) ' storage for the results
    
    startpoint = 0
    Call combinations(startpoint, fromarray, arraycount, choice, outputarray)
    
    ' ##### now output results to Sheet2 ########
    For irow = 1 To numcomb
        For icol = 1 To choice
            Sheets("Sheet2").Cells(irow, icol).Value = outputarray(irow, icol)
        Next
    Next
    MsgBox "Done"
End Sub
Sub combinations(startpoint, source() As Long, size, depth As Long, output() As Long)
    ' do calcs for current level then do recursion
    Dim nextlevel As Long
    nextlevel = depth - 1
    For iloop = startpoint + 1 To size
        outputsinglerow(choice - depth + 1) = source(iloop)
        If depth > 1 Then
            Call combinations(iloop, source, size, nextlevel, output)
        Else
            outputpointer = outputpointer + 1
            If outputpointer > numcomb Then Exit For
            For jloop = 1 To choice
                output(outputpointer, jloop) = outputsinglerow(jloop)
            Next
        End If
    Next
End Sub

 

[cornflakegirl]

If you have 15 numbers, there are 15*14*13*12*11*10 = about 3.6MM six number combinations. Listing *all* of them is going to make a very large file.

That would be permutations, not combinations. For combinations, divide by 6! - only 5005 rows.

xpost with Glenn!

 

[gavbro]

Thanks Glenn but I get an error.<o></o>
<o></o>
I am also confused now, do I want combinations or permutations.<o></o>
<o> </o>
Ok if I have the numbers 1,2,3,4. and wanted three of these<o></o>
<o> </o>
The result I would be looking for is<o></o>
1,2,3<o></o>
1,2,4<o></o>
2,3,4<o></o>
1,3,4<o></o>
<o> </o>
Glenn if yours works OK and I am doing something wrong, could you please mail the excel sheet to <st1ersonname w:st="on">Removed by Moderator</st1ersonname><o></o>
<o> </o>
Gavin<o></o>

 

[cornflakegirl]

Gavin - you want combinations. If you had permutations, 1-2-3 would be distinct from 1-3-2.

Glenn's code doesn't work for me either. I get a "By ref argument type mismatch" error, and it highlights (in blue) the word choice in this line:

Call combinations(startpoint, fromarray, arraycount, choice, outputarray)

 

[gavbro]

Thanks Emma,
And I get the same error.
Hope you can fix it Glenn, would be great if it worked.

Gavin

 

[GlennUK]

I missed this off the top of the module:

Option Base 1
Public outputpointer As Long, outputsinglerow(255) As Long, numcomb As Long, choice As Long