For PAB & GillesD

[bloubul]

Gentlemen,

May I call upon you to try your hand at the following macro for me please.

My database is as follows:
Columns:
A1=Draw No
B1=Date
C1=Ball1
D1=Ball2
E1=Ball3
F1=Ball4
G1=Ball5
H1=Ball6
I1= BB
J1=Min Value
K1=Max Value
Generated lines in columns L2 to Q202

The macro that I'm looking for must use the database as a whole and then generate 200 lines with a allocate Min value as indicated in column J1 and a Max value as indicated in column K1.
The Min and Max Values are pre-determine, it will vary from draw to draw.

Will you Gentlemen please help me.


Thanks

BlouBul

 

[PAB]

Hi BlouBul,

The macro that I'm looking for must use the database as a whole and then generate 200 lines with a allocate Min value as indicated in column J1 and a Max value as indicated in column K1.
The Min and Max Values are pre-determine, it will vary from draw to draw.

I Understand that the WHOLE Database is to be Considered. I Don't Understand where you get the 200 Lines from. Could you also Explain in More Detail the Allocation of the MIN & MAX Values, How are they Pre-Determined for Example, and How will they Change.

All the Best.
PAB

 

[bloubul]

Hi PAB

I hope this will explain it more. The macro must generate 200 lines with new six numbers per line with a Min Value (SUM) of six numbers and Max Value (SUM) of six new numbers. (e.g. 1+2+3+4+5+6=21
Min value (SUM), thus in column J1 21 will be entered, and e.g. 34+35+36+37+38+39=219 Max value (SUM), thus in column K1 219 will be entered. In other words, the first new six numbers Min value (SUM) must be 21 and the last new six numbers Max value (SUM) must be 219. So it must be able to produce the 200 lines with this Min Values (SUM) and Max Values (SUM) taking all 49 numbers into consideration. So for the next draw you choose Min value (SUM) 90 and Max value (SUM) 250 etc. etc.

Thanks

BlouBul

 

[PAB]

Hi BlouBul,

The Min and Max Values are pre-determine, it will vary from draw to draw.

If I Understand Correctly, you want the MIN & MAX Sum Values of the 6 Main Numbers ( Excluding the Bonus Number ) for ALL the Lotto Draws to Date in a 649 Lotto.

You could get the Sum Value of the 6 Main Numbers Using the Formula ( in Cell "J2" for Example ) :-
=SUM(C2:H2)
Then Just Copy this Formula Down as Far as Needed.

Then to get the MIN Sum Value for ALL Draws you could Use the Formula ( in Cell "K2" for Example ) :-
=MIN(J:J)

Then to get the MAX Sum Value for ALL Draws you could Use the Formula ( in Cell "K3" for Example ) :-
=MAX(J:J)

I DON'T Agree that the MIN & MAX Sum Values will Vary from Draw to Draw. The MIN & MAX Sum Values will ONLY Change Once in a Blue Moon, Especially with a Lotto that has Been Established for a While.
Below is a Table of ALL Possible Combinations for EACH Sum Total in a 649 Lotto ( Excluding the Bonus Number ).

Here is the Table :-
21 = 1 Combination
22 = 1 Combination
23 = 2 Combinations
24 = 3 Combinations
25 = 5 Combinations
26 = 7 Combinations
27 = 11 Combinations
28 = 14 Combinations
29 = 20 Combinations
30 = 26 Combinations
31 = 35 Combinations
32 = 44 Combinations
33 = 58 Combinations
34 = 71 Combinations
35 = 90 Combinations
36 = 110 Combinations
37 = 136 Combinations
38 = 163 Combinations
39 = 199 Combinations
40 = 235 Combinations
41 = 282 Combinations
42 = 331 Combinations
43 = 391 Combinations
44 = 454 Combinations
45 = 532 Combinations
46 = 612 Combinations
47 = 709 Combinations
48 = 811 Combinations
49 = 931 Combinations
50 = 1,057 Combinations
51 = 1,206 Combinations
52 = 1,360 Combinations
53 = 1,540 Combinations
54 = 1,729 Combinations
55 = 1,945 Combinations
56 = 2,172 Combinations
57 = 2,432 Combinations
58 = 2,702 Combinations
59 = 3,009 Combinations
60 = 3,331 Combinations
61 = 3,692 Combinations
62 = 4,070 Combinations
63 = 4,494 Combinations
64 = 4,935 Combinations
65 = 5,426 Combinations
66 = 5,940 Combinations
67 = 6,506 Combinations
68 = 7,097 Combinations
69 = 7,748 Combinations
70 = 8,423 Combinations
71 = 9,163 Combinations
72 = 9,933 Combinations
73 = 10,769 Combinations
74 = 11,637 Combinations
75 = 12,579 Combinations
76 = 13,552 Combinations
77 = 14,603 Combinations
78 = 15,690 Combinations
79 = 16,856 Combinations
80 = 18,059 Combinations
81 = 19,349 Combinations
82 = 20,673 Combinations
83 = 22,087 Combinations
84 = 23,540 Combinations
85 = 25,082 Combinations
86 = 26,663 Combinations
87 = 28,340 Combinations
88 = 30,051 Combinations
89 = 31,860 Combinations
90 = 33,706 Combinations
91 = 35,648 Combinations
92 = 37,625 Combinations
93 = 39,703 Combinations
94 = 41,809 Combinations
95 = 44,016 Combinations
96 = 46,253 Combinations
97 = 48,586 Combinations
98 = 50,944 Combinations
99 = 53,402 Combinations
100 = 55,875 Combinations
101 = 58,446 Combinations
102 = 61,031 Combinations
103 = 63,706 Combinations
104 = 66,388 Combinations
105 = 69,161 Combinations
106 = 71,928 Combinations
107 = 74,781 Combinations
108 = 77,624 Combinations
109 = 80,542 Combinations
110 = 83,440 Combinations
111 = 86,412 Combinations
112 = 89,348 Combinations
113 = 92,350 Combinations
114 = 95,311 Combinations
115 = 98,324 Combinations
116 = 101,285 Combinations
117 = 104,295 Combinations
118 = 107,235 Combinations
119 = 110,215 Combinations
120 = 113,119 Combinations
121 = 116,048 Combinations
122 = 118,889 Combinations
123 = 121,751 Combinations
124 = 124,507 Combinations
125 = 127,274 Combinations
126 = 129,930 Combinations
127 = 132,581 Combinations
128 = 135,109 Combinations
129 = 137,629 Combinations
130 = 140,008 Combinations
131 = 142,370 Combinations
132 = 144,587 Combinations
133 = 146,771 Combinations
134 = 148,800 Combinations
135 = 150,794 Combinations
136 = 152,617 Combinations
137 = 154,397 Combinations
138 = 156,004 Combinations
139 = 157,554 Combinations
140 = 158,923 Combinations
141 = 160,236 Combinations
142 = 161,354 Combinations
143 = 162,410 Combinations
144 = 163,273 Combinations
145 = 164,062 Combinations
146 = 164,654 Combinations
147 = 165,176 Combinations
148 = 165,490 Combinations
149 = 165,732 Combinations
150 = 165,772 Combinations
151 = 165,732 Combinations
152 = 165,490 Combinations
153 = 165,176 Combinations
154 = 164,654 Combinations
155 = 164,062 Combinations
156 = 163,273 Combinations
157 = 162,410 Combinations
158 = 161,354 Combinations
159 = 160,236 Combinations
160 = 158,923 Combinations
161 = 157,554 Combinations
162 = 156,004 Combinations
163 = 154,397 Combinations
164 = 152,617 Combinations
165 = 150,794 Combinations
166 = 148,800 Combinations
167 = 146,771 Combinations
168 = 144,587 Combinations
169 = 142,370 Combinations
170 = 140,008 Combinations
171 = 137,629 Combinations
172 = 135,109 Combinations
173 = 132,581 Combinations
174 = 129,930 Combinations
175 = 127,274 Combinations
176 = 124,507 Combinations
177 = 121,751 Combinations
178 = 118,889 Combinations
179 = 116,048 Combinations
180 = 113,119 Combinations
181 = 110,215 Combinations
182 = 107,235 Combinations
183 = 104,295 Combinations
184 = 101,285 Combinations
185 = 98,324 Combinations
186 = 95,311 Combinations
187 = 92,350 Combinations
188 = 89,348 Combinations
189 = 86,412 Combinations
190 = 83,440 Combinations
191 = 80,542 Combinations
192 = 77,624 Combinations
193 = 74,781 Combinations
194 = 71,928 Combinations
195 = 69,161 Combinations
196 = 66,388 Combinations
197 = 63,706 Combinations
198 = 61,031 Combinations
199 = 58,446 Combinations
200 = 55,875 Combinations
201 = 53,402 Combinations
202 = 50,944 Combinations
203 = 48,586 Combinations
204 = 46,253 Combinations
205 = 44,016 Combinations
206 = 41,809 Combinations
207 = 39,703 Combinations
208 = 37,625 Combinations
209 = 35,648 Combinations
210 = 33,706 Combinations
211 = 31,860 Combinations
212 = 30,051 Combinations
213 = 28,340 Combinations
214 = 26,663 Combinations
215 = 25,082 Combinations
216 = 23,540 Combinations
217 = 22,087 Combinations
218 = 20,673 Combinations
219 = 19,349 Combinations
220 = 18,059 Combinations
221 = 16,856 Combinations
222 = 15,690 Combinations
223 = 14,603 Combinations
224 = 13,552 Combinations
225 = 12,579 Combinations
226 = 11,637 Combinations
227 = 10,769 Combinations
228 = 9,933 Combinations
229 = 9,163 Combinations
230 = 8,423 Combinations
231 = 7,748 Combinations
232 = 7,097 Combinations
233 = 6,506 Combinations
234 = 5,940 Combinations
235 = 5,426 Combinations
236 = 4,935 Combinations
237 = 4,494 Combinations
238 = 4,070 Combinations
239 = 3,692 Combinations
240 = 3,331 Combinations
241 = 3,009 Combinations
242 = 2,702 Combinations
243 = 2,432 Combinations
244 = 2,172 Combinations
245 = 1,945 Combinations
246 = 1,729 Combinations
247 = 1,540 Combinations
248 = 1,360 Combinations
249 = 1,206 Combinations
250 = 1,057 Combinations
251 = 931 Combinations
252 = 811 Combinations
253 = 709 Combinations
254 = 612 Combinations
255 = 532 Combinations
256 = 454 Combinations
257 = 391 Combinations
258 = 331 Combinations
259 = 282 Combinations
260 = 235 Combinations
261 = 199 Combinations
262 = 163 Combinations
263 = 136 Combinations
264 = 110 Combinations
265 = 90 Combinations
266 = 71 Combinations
267 = 58 Combinations
268 = 44 Combinations
269 = 35 Combinations
270 = 26 Combinations
271 = 20 Combinations
272 = 14 Combinations
273 = 11 Combinations
274 = 7 Combinations
275 = 5 Combinations
276 = 3 Combinations
277 = 2 Combinations
278 = 1 Combination
279 = 1 Combination
Total Combinations = 13,983,816

It is NOT Going to be Easy to JUST List 200 New 6 Number Combinations that Fall Between the Current MIN & MAX Sum Values to Date, this is Because of the Total Number of Combinations Associated with EACH Sum Total in a 649 Lotto.
For Example, if the Current MIN Sum Value was 33 ( which Produces 58 Combinations ) & the Current MAX Sum Value was 120 ( which Produces 113,119 Combinations ), there would be a Total of 2,639,923 Combinations. How would you want to Pick ONLY 200 Combinations from those.
Obviously the Above is Only Relevant if I have Understood your Questions and Requirements.

Hope this Helps.
All the Best.
PAB

 

[bloubul]

PAB

Thanks for the load of info. I agree with you that it would be difficult to select 200 lines, and I maybe also be far of the track here. I don't know of any Software that can produce only lines with a pre-set MIN SUM and a MAX SUM, maybe you do. The 200 lines are only an experiment because I'm looking for a MIN SUM of 90 and a MAX SUM of 220.

BlouBul

 

[PAB]

Hi BlouBul,

I agree with you that it would be difficult to select 200 lines, and I maybe also be far of the track here.

Unless you have VERY Tight Parameters ( Filters ) that you are Going to Apply to the MANY Lines, I cannot see that this is Achievable.

The 200 lines are only an experiment because I'm looking for a MIN SUM of 90 and a MAX SUM of 220.

The Total Combinations Available with a Sum Total of 90 is 33,706.
The Total Combinations Available with a Sum Total of 220 is 18,059.
The Total Combinations Available with a Sum Total from 90 to 220 Inclusive is 13,334,352.
ALL these are Based on a 649 Lotto Excluding a Bonus Number.

All the Best.
PAB

 

[bloubul]

PAB

The whole idea came about with a macro GillesD wrote for some one on this BB. In fact he wrote 2, but I can't find it now, but here is that macro which I'm interested again, GillesD if I transgress please for give me.

Option Explicit
Dim A As Integer, B As Integer, C As Integer
Dim D As Integer, E As Integer, I As Integer
Dim minA As Integer, maxA As Integer, minB As Integer, maxB As Integer
Dim minC As Integer, maxC As Integer, minD As Integer, maxD As Integer
Dim minE As Integer, maxE As Integer
Dim nA(6) As Integer, nB(6) As Integer, nC(6) As Integer
Dim nD(6) As Integer, nE(6) As Integer

Sub CombFromGroups()
Sheets("Sheet1").Select
Range("A1").Select
For I = 1 To 6
nA(I) = ActiveCell.Offset(I, 0).Value
nB(I) = ActiveCell.Offset(I, 1).Value
nC(I) = ActiveCell.Offset(I, 2).Value
nD(I) = ActiveCell.Offset(I, 3).Value
nE(I) = ActiveCell.Offset(I, 4).Value
Next I
minA = nA(1)
minB = nB(1)
minC = nC(1)
minD = nD(1)
minE = nE(1)
maxA = Application.WorksheetFunction.Max(nA(1), nA(2), nA(3), nA(4), nA(5), nA(6))
maxB = Application.WorksheetFunction.Max(nB(1), nB(2), nB(3), nB(4), nB(5), nB(6))
maxC = Application.WorksheetFunction.Max(nC(1), nC(2), nC(3), nC(4), nC(5), nC(6))
maxD = Application.WorksheetFunction.Max(nD(1), nD(2), nD(3), nD(4), nD(5), nD(6))
maxE = Application.WorksheetFunction.Max(nE(1), nE(2), nE(3), nE(4), nE(5), nE(6))
I = 1
Sheets("Sheet2").Select
Application.ScreenUpdating = False
Range("A2.F7777").Delete
Range("A1").Select
For A = minA To maxA
For B = minB To maxB
For C = minC To maxC
For D = minD To maxD
For E = minE To maxE
Select Case A
Case nA(1), nA(2), nA(3), nA(4), nA(5), nA(6)
Select Case B
Case nB(1), nB(2), nB(3), nB(4), nB(5), nB(6)
Select Case C
Case nC(1), nC(2), nC(3), nC(4), nC(5), nC(6)
Select Case D
Case nD(1), nD(2), nD(3), nD(4), nD(5), nD(6)
Select Case E
Case nE(1), nE(2), nE(3), nE(4), nE(5), nE(6)
ActiveCell.Offset(I, 0).Value = I
ActiveCell.Offset(I, 1).Value = A
ActiveCell.Offset(I, 2).Value = B
ActiveCell.Offset(I, 3).Value = C
ActiveCell.Offset(I, 4).Value = D
ActiveCell.Offset(I, 5).Value = E
I = I + 1
End Select
End Select
End Select
End Select
End Select
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

If this macro produce some long lines of new numbers, maybe it is possible to make a few changes to it to do what I wan't. Filters has cross my mind but with nothing in hand I cannot tell what or how to use it.

BlouBul

 

[PAB]

Hi BlouBul,

The Macro Written by GillesD you Posted has NOTHING to do with the MINIMUM & MAXIMUM Sum Total of a Combination(s).
The Macro Allows you to Put a Minimum of 1 Number and a Maximum of 6 Numbers in 5 Groups ( Groups A to E ), it then Produces Combinations Accordingly ( Using 1 Number from EACH Group ).

All the Best.
PAB

 

[bloubul]

PAB

Thanks for all the information & help.

BlouBul