Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Ms Nosizwe View Post
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()
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 74
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
combinations of numbers sum to a given amount? TimH Excel Worksheet Functions 3 October 16th 10 10:59 PM
Counting combinations of numbers nitrousdave Excel Discussion (Misc queries) 4 December 21st 07 07:12 AM
solving for all possible combinations of a set of numbers tradersm Excel Worksheet Functions 10 July 15th 05 12:36 AM
combining numbers into combinations David Excel Worksheet Functions 1 May 30th 05 03:24 AM
Function generating all possible combinations of set of numbers Lucia Excel Worksheet Functions 1 February 7th 05 11:41 PM


All times are GMT +1. The time now is 06:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"