Excel files needed combinations by position

S

serge

Member
Hello,

I'd like to know if someone has or can create this VBA code 5/56 combinations by number position,with their lexicographic number on front of each combination,there is a total of 260 numbers by position.

It would look like this if someone were to do it.

firstable all columns would be at width : 15.

Details for the file display.

For position 1.

1. Cell A1 and B1 would be " merge " together with in it : Position 1.

2. Row 2 would be empty.

3. Cell A3 and B3 would also be " merge : with the number 1 in it.

4. Row 4 would be empty.

5. Cell A5 would have the word : Lexicographic in it.

6. Cell B5 would have the word : Combinations in it.

7. Row 6 would be empty.

8. Cell A7 and up would have the lexicographic numbers list from the combinations of the column B.

9. Cell B7 and up ( column ) would have all the combinations for the number 1 " as position1 ".



It should look like this :



For all the numbers as " position 1 ". (1 to 52)

It will start at columns : A,B with number 1 and will stop at columns : CY,CZ with number : 52.

For all the numbers as " position 2 ". (2 to 53)

It will start at columns : DA,DB with number 2 and will stop at columns : GY,GZ with number : 53.

For all the numbers as " position 3 ". (3 to 54)

It will start at columns : HA,HB with number 3 and will stop at columns : KY,KZ with number : 54.

For all the numbers as " position 4 ". (4 to 55)

It will start at columns : LA,LB with number 4 and will stop at columns : OY,OZ with number : 55.

For all the numbers as " position 5 ". (5 to 56)

It will start at columns : PA,PB with number 5 and will stop at columns : SY,SZ with number : 56.

IF I DIDN'T MAKE MISTAKE IT SHOULD BE RIGHT !!!

If someone can write this Excel file,I would realy appreciate it, Thank you.
Serge.
 
bloubul

bloubul

Member
Hi serge

Please upload your spreadsheet for me as you have laid it out. And let me have the link.

BlouBul :cool:
 
S

serge

Member
Hi Bloubul,

Sorry for the delay I was out of town but here is the link :

http://www.mediafire.com/?cuynlg5fy7b13jg

And if you activate the macro in the tab " List556Combos " it will generate all 3 819 816 combinations for the Mega Millions game it will take aproximatly 10 to 15 mns for an average computer and I think over 100 Mb.
But all the instructions are in the file and if you have a better way go for it.

I'm not to sure about " media fire " if I did it right it's my first time I using it !!!

Thank you in advance. Serge.
 
K

Kenya649

Member
Hi Serge and Bloubul.
You can use the following Macro which will ask you to put Highest ball value. For your case 5/56 Maximum BALL number is 56.
NOTE: You can start with small BALL numbers value (10-20) as 56 will take long to compute. I did not run it myself though I'm sure it will work

Sub Button1_Click()
x1 = InputBox("Please enter the maximum BALL number")
i = 0
j = 1
For i1 = 1 To x1 - 4
k = 0
j = j + 7
For i2 = i1 + 1 To x1 - 3
For i3 = i2 + 1 To x1 - 2
For i4 = i3 + 1 To x1 - 1
For i5 = i4 + 1 To x1 - 0
i = i + 1
k = k + 1
Range("j7").Offset(k, j - 1).Value = i
Range("j7").Offset(k, j).Value = i1
Range("j7").Offset(k, j + 1).Value = i2
Range("j7").Offset(k, j + 2).Value = i3
Range("j7").Offset(k, j + 3).Value = i4
Range("j7").Offset(k, j + 4).Value = i5
Next
Next
Next
Next
Next
End Sub
 
bloubul

bloubul

Member
Hi serge
I trust that it will run, but how are going to make your selections, I see PAB is also logged on and he is also a master with macro's

BlouBul :cool:
 
S

serge

Member
Hi Kenya,

Thank you very much Kenya for the macro it work perfectly,I'm impress by your work I which I could do the same but don't have the knowledge !!!

This macro work fine for the 1st position of numbers,are you gone make the macro for the 2nd position ? and for the 3rd,4th and 5th ?

Each position total 3,819,816 combinations plus their lexicographic numbers, So I think it's good to have one excel spread sheet for each position ? you tell me ?

I hope you can help me with the other ones, I really need them for my elimination system.
It took my computer 25 mns to generate all the combinations with your macro.

Download the Excel file from my link above if you haven't done it yet and check the tab : Combinatorial Distribution " and you'll see, on the second position this table below :

Position 2
1
2 24804
3 46852
4 66300
5 83300
6 98000
7 110544
8 121072
9 129720
10 136620
11 141900
12 145684
13 148092
14 149240
15 149240
16 148200
17 146224
18 143412
19 139860
20 135660
21 130900
22 125664
23 120032
24 114080
25 107880
26 101500
27 95004
28 88452
29 81900
30 75400
31 69000
32 62744
33 56672
34 50820
35 45220
36 39900
37 34884
38 30192
39 25840
40 21840
41 18200
42 14924
43 12012
44 9460
45 7260
46 5400
47 3864
48 2632
49 1680
50 980
51 500
52 204
53 52
54
55
56

Thanks again and let me know. Serge.
 
S

serge

Member
Thank you to you also bloubul,

if you think PAB can help he is welcome, I don't know anybody on this site I'm kind knew, but I take any help possible.

Thank you all if you can help ? Serge.
 
K

Kenya649

Member
Hi,

The macro should run for all the number positions starting at cell Q8. Please put a small value like 10 and see what happens

Please share your elimination method with us

01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 32 33 34 35
36 37 38 39 40 41 42
43 44 45 46 47 48 49


regards
 
K

Kenya649

Member
Hi,

The macro should run for all the number positions starting at cell Q8. Please put a small value like 10 and see what happens

Please share your elimination method with us

01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31 32 33 34 35
36 37 38 39 40 41 42
43 44 45 46 47 48 49

consider eliminating such numbers 3,7,15,19,31,35 as they form a pattern on 7x7 49 number grid if you were to draw a line from 15-3,15-31 ;3-19,31-19; 19-7,19-15;7-35. you will the the pattern clearly

regards
 
M

mash

Member
Please try the following Macro

Sub combosbypositions()
Dim x(5) As Byte
Dim v(5) As Byte
Dim row(5) As Long
Dim rec As Long
Dim i, j, k As Byte
Range("A:AH").ClearContents
num = InputBox("Please Enter Highest Ball number ")
For i = 1 To 5
x(i) = InputBox("Please Enter Number for Position : " & i)
Next
For i = 1 To 5
row(i) = 1
Next
j = 0
For i = 1 To 29 Step 7
j = j + 1
Range("A1").Offset(0, i).Value = "Position" & j & " # " & x(j)
Range("A1").Offset(0, i - 1).Value = "LEX"
Next
For i1 = 1 To num - 4
For i2 = i1 + 1 To num - 3
For i3 = i2 + 1 To num - 2
For i4 = i3 + 1 To num - 1
For i5 = i4 + 1 To num - 0
v(1) = i1
v(2) = i2
v(3) = i3
v(4) = i4
v(5) = i5
rec = rec + 1
For k = 1 To 5
If v(k) = x(k) Then
If k = 1 Then Range("A1").Offset(row(k), 0).Value = rec
If k = 2 Then Range("A1").Offset(row(k), 7).Value = rec
If k = 3 Then Range("A1").Offset(row(k), 14).Value = rec
If k = 4 Then Range("A1").Offset(row(k), 21).Value = rec
If k = 5 Then Range("A1").Offset(row(k), 28).Value = rec
For j = 1 To 5
If k = 1 Then Range("A1").Offset(row(k), j).Value = v(j)
If k = 2 Then Range("A1").Offset(row(k), 7 + j).Value = v(j)
If k = 3 Then Range("A1").Offset(row(k), 14 + j).Value = v(j)
If k = 4 Then Range("A1").Offset(row(k), 21 + j).Value = v(j)
If k = 5 Then Range("A1").Offset(row(k), 28 + j).Value = v(j)
Next
row(k) = row(k) + 1
End If
Next
Next
Next
Next
Next
Next
End Sub

:beer:
 
K

Kenya649

Member
Hope the following helps


Sub Button1_Click()
Dim x(5)
Dim rx(51) As Long
Dim row, col, r As Long
Dim rec As String
num = InputBox("Please Enter Highest Ball number")
If Val(num) = 0 Then Exit Sub
pos = InputBox("Please Enter Ball Position you wish to Compute 1-5")
If Val(pos) < 1 Or Val(pos) > 5 Then Exit Sub
Range("A9:mY1048576") = Empty
For i = 1 To 51
rx(i) = 8
Next
For i1 = 1 To num - 4
For i2 = i1 + 1 To num - 3
For i3 = i2 + 1 To num - 2
For i4 = i3 + 1 To num - 1
For i5 = i4 + 1 To num - 0
x(1) = i1
x(2) = i2
x(3) = i3
x(4) = i4
x(5) = i5
r = r + 1
col = 1
For j = 1 To 51
'For i = 1 To 5
If x(pos) = j Then
If Len(r) < 7 Then
rec = "'0"
For k = 1 To 7 - Len(r) - 1
rec = rec & "0"
Next
rec = rec & r
End If
Range("A1").Offset(rx(j), col).Value = rec
For k = 1 To 5
Range("A1").Offset(rx(j), k + col).Value = x(k)
Next
rx(j) = rx(j) + 1
End If
'Next
col = col + 7
row = 8 + 1
Next
Next
Next
Next
Next
Next
MsgBox "Process Completed"
End Sub

thanks
:lphant:
 
You must log in or register to reply here.

Sidebar

Top