# Number Combinations

#### gavbro

##### New Member
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>

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### Oaktree

##### MrExcel MVP
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

##### MrExcel MVP
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.
Code:
``````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

##### New Member
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

##### Well-known Member
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.

Code:
``````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

##### Well-known Member
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

##### New Member
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>

Last edited by a moderator:

#### cornflakegirl

##### Well-known Member
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:
Code:
``Call combinations(startpoint, fromarray, arraycount, choice, outputarray)``

#### gavbro

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

Gavin

#### GlennUK

##### Well-known Member
I missed this off the top of the module:
Code:
``````Option Base 1
Public outputpointer As Long, outputsinglerow(255) As Long, numcomb As Long, choice As Long``````

Replies
0
Views
95
Replies
0
Views
101
Replies
3
Views
234
Replies
1
Views
327
Replies
4
Views
129

1,191,534
Messages
5,987,142
Members
440,082
Latest member
belodelokelo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back