Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
how can i work out all the possible 6 number combinations from 1-36 (similar to a lottery ticket - pick 6 from 36).
|
#2
|
|||
|
|||
Look up how to use =PERMUT()
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
Ms Nosizwe formulated the question :
how can i work out all the possible 6 number combinations from 1-36 (similar to a lottery ticket - pick 6 from 36). Try searching online for "lotto wheeling". You should find lots to work with... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
After serious thinking Spencer101 wrote :
Ms Nosizwe;1607415 Wrote: how can i work out all the possible 6 number combinations from 1-36 (similar to a lottery ticket - pick 6 from 36). Look up how to use =PERMUT() This will return the total number of possible combinations, NOT the actual combinations. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
After serious thinking Ms Nosizwe wrote :
how can i work out all the possible 6 number combinations from 1-36 (similar to a lottery ticket - pick 6 from 36). This generates and prints all C(Elements, Class) combinations in lexicographic order. ============================================= Public Sub CombinazioniS() Dim i As Long, j As Long, k As Long, FactClass As Long, n As Long Dim CS() As Long, NumComb As Long, Elements As Long, Class As Long Dim TargetRange As Range, S As String, RowsPerColumn As Long, T As Double ' Definition ------------------------- Elements = 36 Class = 6 Set TargetRange = [Sheet10!CK25] RowsPerColumn = 500000 ' Printing Layout ' ------------------------------------ T = Timer ' NumComb = Numero delle combinazioni ' ------------------------------------ NumComb = 1 For i = Elements To Elements - Class + 1 Step -1 NumComb = NumComb * i Next FactClass = 1 For i = Class To 2 Step -1 FactClass = FactClass * i Next NumComb = NumComb / FactClass ' ------------------------------------- ReDim CS(1 To NumComb, 1 To Class) For i = 1 To Class CS(1, i) = i Next For i = 2 To NumComb k = Class Do Until CS(i - 1, k) < Elements - Class + k k = k - 1 Loop For j = 1 To k - 1 CS(i, j) = CS(i - 1, j) Next CS(i, k) = CS(i - 1, k) + 1 For j = k + 1 To Class CS(i, j) = CS(i, j - 1) + 1 Next Next ' Stampa in TargetRange-down Application.Calculation = xlCalculationManual Application.ScreenUpdating = False n = 0: k = 1 For i = 1 To UBound(CS, 1) S = "" For j = 1 To UBound(CS, 2) S = S & CS(i, j) & " " Next 'MsgBox S n = n + 1 TargetRange(n, k) = S If i Mod RowsPerColumn = 0 Then k = k + 1 n = 0 End If Next Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True MsgBox Timer - T End Sub ========================================== Bruno |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
"Spencer101" wrote:
Ms Nosizwe;1607415 Wrote: how can i work out all the possible 6 number combinations from 1-36 (similar to a lottery ticket - pick 6 from 36). Look up how to use =PERMUT() The correct count is probably COMBIN(36,6). PERMUT() counts the number of arrangements taking order into account. That would be appropriate for a Pick-N game. But Pick-N games typically use only the digits 0 through 9. COMBIN() counts the number arrangements ignoring order. That is typical of lotto games like the Canadian Jour du Paye game, which is probably what "Ms Nosizwe" is asking about. In any case, that only __counts__ the number of combination. The following macro generates all n-choose-k combinations ignoring order, which might be what "Ms Nosizwe" meant by "work out all possible combinations". ----- Option Explicit Sub combinKofN() Dim n As Long, k As Long Dim maxCombin As Long, nCombin As Long Dim maxRow As Long, nRow As Long Dim i As Long, j As Long Dim resRng As Range Dim st As Single, et As Single st = Timer With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With ' input parameters n = Range("a1") k = Range("a2") ' output location Range("b1", Cells(1, Columns.Count)).EntireColumn.Delete Set resRng = Range("b1") maxCombin = WorksheetFunction.Combin(n, k) maxRow = Rows.Count If maxRow maxCombin Then maxRow = maxCombin ' set of numbers 1 to n ReDim mySet(1 To n) As Long For i = 1 To n: mySet(i) = i: Next ' set of indexes for mySet ReDim idx(1 To k) As Long For i = 1 To k: idx(i) = i: Next ' results ReDim myCombin(1 To maxRow, 1 To k) As Long nCombin = 0: nRow = 0 Do ' record combination nRow = nRow + 1 For i = 1 To k myCombin(nRow, i) = mySet(idx(i)) Next nCombin = nCombin + 1 If nCombin = maxCombin Then GoTo showResults If nRow = maxRow Then ' output group of combinations With resRng.Resize(nRow, k) .Value = myCombin .EntireColumn.AutoFit End With ' separate groups by one column resRng.Offset(0, k).EntireColumn.ColumnWidth = _ resRng.Offset(0, k - 1).ColumnWidth Set resRng = resRng.Offset(0, k + 1) nRow = 0 DoEvents End If ' next combination i = k: j = 0 While idx(i) = n - j i = i - 1: j = j + 1 Wend idx(i) = idx(i) + 1 For j = i + 1 To k idx(j) = idx(j - 1) + 1 Next Loop showResults: ' output combinations With resRng.Resize(nRow, k) .Value = myCombin .EntireColumn.AutoFit End With With Application .EnableEvents = True .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With et = Timer MsgBox "done: " & Format(et - st, "0.000") & " sec" End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all the possible 6 number combinations from numbers 1-36
Joe,
Very nice! Perhaps it could be modified to do Pick n so it includes zero. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combinations of numbers sum to a given amount? | Excel Worksheet Functions | |||
Counting combinations of numbers | Excel Discussion (Misc queries) | |||
solving for all possible combinations of a set of numbers | Excel Worksheet Functions | |||
combining numbers into combinations | Excel Worksheet Functions | |||
Function generating all possible combinations of set of numbers | Excel Worksheet Functions |