How To Reduce Combination Vba
- Thread starter Hoareau
- Start date
IT S THE SAME METHODE THAT HORSE RACING
I want to apply the same method while taking as bases last pullings
SEE MACRO INCOMPLETE CI BELOW THANK YOU
The principle You have one or more favourites in the race (your basic horses) and you wish to associate your selection: others leaving the race: you play then Quarté+ Champ reduced, all leaving them the race: you play then Quarté+ Champ total.
Example
When you play Quarté+ with 2 favorite horses in the first 2 places (3-6)
to which you associate 3 other horses for 3rd and 4th places of the arrival (4-1-5),
you play Quarté+ Champ reduced with 2 basic horses and 3 associated horses.
That amounts playing all Quarté+ of 4 horses formed by your 2 basic horses in the first 2 places associated
with the 3 other horses taken 2 to 2 in the 2 possible orders.
You play actually the 6 Quarté+ unit following:
3-6-4-1
3-6-1-4
3-6-4-5
3-6-5-4
3-6-1-5
3-6-5-1
Is the price of this Quarté+ Champ reduced with 2 basic horses thus 7,8? (6X1,3?).
If the arrival of the race is 3-6-4-1, you found the first 4 horses of the arrival in the order and the disorder.
You gain once the order report/ratio, once the disorder report/ratio and the Bonus report/ratio! More formula Field in Quarté+
You play into only once several unit bets comprising each one your or your favorite horses.
Sub Macro1()
Dim lig
Dim col
For col = 6 To 9
For lig = 5 To 210
If Cells(lig, col) = Cells(lig + 1, col) Then
Cells(lig, col + 6) = Cells(lig, col)
Else
For I = 1 To 1
Cells(lig, col + 10) = Cells(lig, col)
Next
End If
Next
Next
End Sub
I want to apply the same method while taking as bases last pullings
SEE MACRO INCOMPLETE CI BELOW THANK YOU
The principle You have one or more favourites in the race (your basic horses) and you wish to associate your selection: others leaving the race: you play then Quarté+ Champ reduced, all leaving them the race: you play then Quarté+ Champ total.
Example
When you play Quarté+ with 2 favorite horses in the first 2 places (3-6)
to which you associate 3 other horses for 3rd and 4th places of the arrival (4-1-5),
you play Quarté+ Champ reduced with 2 basic horses and 3 associated horses.
That amounts playing all Quarté+ of 4 horses formed by your 2 basic horses in the first 2 places associated
with the 3 other horses taken 2 to 2 in the 2 possible orders.
You play actually the 6 Quarté+ unit following:
3-6-4-1
3-6-1-4
3-6-4-5
3-6-5-4
3-6-1-5
3-6-5-1
Is the price of this Quarté+ Champ reduced with 2 basic horses thus 7,8? (6X1,3?).
If the arrival of the race is 3-6-4-1, you found the first 4 horses of the arrival in the order and the disorder.
You gain once the order report/ratio, once the disorder report/ratio and the Bonus report/ratio! More formula Field in Quarté+
You play into only once several unit bets comprising each one your or your favorite horses.
Sub Macro1()
Dim lig
Dim col
For col = 6 To 9
For lig = 5 To 210
If Cells(lig, col) = Cells(lig + 1, col) Then
Cells(lig, col + 6) = Cells(lig, col)
Else
For I = 1 To 1
Cells(lig, col + 10) = Cells(lig, col)
Next
End If
Next
Next
End Sub
lottomaniac649
Member
Hello Hoareau;
It looks very much interesting but could not fully understandable. Let me expalin what I understood. You're going to keep certain favourites numbers constant and some number as variables. Then, the combination would emerge exponentialy. One more question. Why you're limiting to 4 number what will you for next two if for a 649 game.
LM649
P.S.: I believe that GillesD would have understood better and provide the solution.
It looks very much interesting but could not fully understandable. Let me expalin what I understood. You're going to keep certain favourites numbers constant and some number as variables. Then, the combination would emerge exponentialy. One more question. Why you're limiting to 4 number what will you for next two if for a 649 game.
LM649
P.S.: I believe that GillesD would have understood better and provide the solution.
time*treat
Member
favorite horses
The editor, here, may wrap or unalighn the text.
The proper code occupies ~80 lines.
Copy & paste, then correct for text wrap.
Re-indent to make it pretty, again.
Sub fav__horses()
'by time*treat ~ Sat., Apr/21/2007'
Dim total__horses As Integer, num__of__faves As Integer
Dim gates() As Integer, row As Integer, col As Integer
Dim winners As Integer, losers As Integer
Dim win__val As Long, lose__val As Long
Dim horse As Integer, stall As Integer
Dim winner__gates() As Integer, loser__gates() As Integer
Dim W__conflict__flag As Boolean, L__conflict__flag As Boolean, c__num As Long
Const light__red As Integer = 38
Const light__green As Integer = 35
'good for up to 10 horses'
total__horses = 6: num__of__faves = 2
ReDim gates(total__horses)
winners = num__of__faves
losers = total__horses - num__of__faves
ReDim winner__gates(winners)
ReDim loser__gates(losers)
c__num = 0: Cells.Clear
'left side - arrangement of winners'
For win__val = 1 To 10 ^ winners - 1
W__conflict__flag = False
For horse = 1 To winners: winner__gates(horse) = 0: Next horse
For horse = 1 To winners
stall = Int((win__val Mod 10 ^ (winners + 1 - horse)) / 10 ^ (winners - horse))
If stall <= winners Then
If stall = 0 Or winner__gates(stall) <> 0 Then
W__conflict__flag = True
Exit For
Else
winner__gates(stall) = horse
End If
Else
W__conflict__flag = True
End If 'stall <= winners'
Next horse
If W__conflict__flag = False Then
'right side - arrangement of losers'
For lose__val = 1 To 10 ^ losers - 1
L__conflict__flag = False
For horse = 1 To losers: loser__gates(horse) = 0: Next horse
For horse = 1 To losers
stall = Int((lose__val Mod 10 ^ (losers + 1 - horse)) / 10 ^ (losers - horse))
If stall <= losers Then
If stall = 0 Or loser__gates(stall) <> 0 Then
L__conflict__flag = True
Exit For
Else
loser__gates(stall) = horse
End If
Else
L__conflict__flag = True
End If 'stall <= losers'
Next horse
If L__conflict__flag = False Then
c__num = c__num + 1
Cells(c__num + 1, 1).Value = "#" & c__num
For col = 1 To winners
Cells(c__num + 1, col + 1).Value = winner__gates(col)
Cells(c__num + 1, col + 1).Interior.ColorIndex = light__green
Next col
For col = 1 To losers
Cells(c__num + 1, col + 1 + num__of__faves).Value = loser__gates(col) + num__of__faves
Cells(c__num + 1, col + 1 + num__of__faves).Interior.ColorIndex = light__red
Next col
End If 'L__conflict__flag'
Next lose__val
End If 'W__conflict__flag'
Next win__val
Cells(1, 1).Value = "~" & c__num & "~"
Cells.EntireColumn.AutoFit
End Sub
The editor, here, may wrap or unalighn the text.
The proper code occupies ~80 lines.
Copy & paste, then correct for text wrap.
Re-indent to make it pretty, again.
Sub fav__horses()
'by time*treat ~ Sat., Apr/21/2007'
Dim total__horses As Integer, num__of__faves As Integer
Dim gates() As Integer, row As Integer, col As Integer
Dim winners As Integer, losers As Integer
Dim win__val As Long, lose__val As Long
Dim horse As Integer, stall As Integer
Dim winner__gates() As Integer, loser__gates() As Integer
Dim W__conflict__flag As Boolean, L__conflict__flag As Boolean, c__num As Long
Const light__red As Integer = 38
Const light__green As Integer = 35
'good for up to 10 horses'
total__horses = 6: num__of__faves = 2
ReDim gates(total__horses)
winners = num__of__faves
losers = total__horses - num__of__faves
ReDim winner__gates(winners)
ReDim loser__gates(losers)
c__num = 0: Cells.Clear
'left side - arrangement of winners'
For win__val = 1 To 10 ^ winners - 1
W__conflict__flag = False
For horse = 1 To winners: winner__gates(horse) = 0: Next horse
For horse = 1 To winners
stall = Int((win__val Mod 10 ^ (winners + 1 - horse)) / 10 ^ (winners - horse))
If stall <= winners Then
If stall = 0 Or winner__gates(stall) <> 0 Then
W__conflict__flag = True
Exit For
Else
winner__gates(stall) = horse
End If
Else
W__conflict__flag = True
End If 'stall <= winners'
Next horse
If W__conflict__flag = False Then
'right side - arrangement of losers'
For lose__val = 1 To 10 ^ losers - 1
L__conflict__flag = False
For horse = 1 To losers: loser__gates(horse) = 0: Next horse
For horse = 1 To losers
stall = Int((lose__val Mod 10 ^ (losers + 1 - horse)) / 10 ^ (losers - horse))
If stall <= losers Then
If stall = 0 Or loser__gates(stall) <> 0 Then
L__conflict__flag = True
Exit For
Else
loser__gates(stall) = horse
End If
Else
L__conflict__flag = True
End If 'stall <= losers'
Next horse
If L__conflict__flag = False Then
c__num = c__num + 1
Cells(c__num + 1, 1).Value = "#" & c__num
For col = 1 To winners
Cells(c__num + 1, col + 1).Value = winner__gates(col)
Cells(c__num + 1, col + 1).Interior.ColorIndex = light__green
Next col
For col = 1 To losers
Cells(c__num + 1, col + 1 + num__of__faves).Value = loser__gates(col) + num__of__faves
Cells(c__num + 1, col + 1 + num__of__faves).Interior.ColorIndex = light__red
Next col
End If 'L__conflict__flag'
Next lose__val
End If 'W__conflict__flag'
Next win__val
Cells(1, 1).Value = "~" & c__num & "~"
Cells.EntireColumn.AutoFit
End Sub
Hello
Thank you for your code, but the number of favourite is not forcing 2 but that depends on the combination of the lower parts and the maximum of leaving is 20.
COLUMNS TO TREAT F With I
the presentation owes that below, the combinations of 4 must be recopied on the sheet of result
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 > Empty row
3 4 5 15 >
3 4 6 12 5 15
Put my macro incomplete in Excel sheet with the data, it is the beginning of this I would like
did not think that that gave as much work
Thank you for your code, but the number of favourite is not forcing 2 but that depends on the combination of the lower parts and the maximum of leaving is 20.
COLUMNS TO TREAT F With I
the presentation owes that below, the combinations of 4 must be recopied on the sheet of result
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 > Empty row
3 4 5 15 >
3 4 6 12 5 15
Put my macro incomplete in Excel sheet with the data, it is the beginning of this I would like
did not think that that gave as much work
time*treat
Member
changing horses
Not sure I could have made it easier... it is just a template.
There are only 2 numbers you need to touch.
total__horses (maximum = 10, minimum = 2)
num__of__faves (maximum = total__horses -1, minimum = 1)
(minimum)
total__horses = 2: num__of__faves = 1
(typical)
total__horses = 7: num__of__faves = 3
(excel can't show all of these)
total__horses = 10: num__of__faves = 1
total__horses = 10: num__of__faves = 9
This macro will calculate much more, but Excel can only give you ~65,536 rows. If you want more than 10 horses, that is much more complex. If you wish to call your horses "6, Blue49, and Fred" rather than "1, 2, & 3", then you can simply rename them after the calculation is run. You can, of course, use other filters to drop combinations you don't like. This should get you well on your way.
Not sure I could have made it easier... it is just a template.
There are only 2 numbers you need to touch.
total__horses (maximum = 10, minimum = 2)
num__of__faves (maximum = total__horses -1, minimum = 1)
(minimum)
total__horses = 2: num__of__faves = 1
(typical)
total__horses = 7: num__of__faves = 3
(excel can't show all of these)
total__horses = 10: num__of__faves = 1
total__horses = 10: num__of__faves = 9
This macro will calculate much more, but Excel can only give you ~65,536 rows. If you want more than 10 horses, that is much more complex. If you wish to call your horses "6, Blue49, and Fred" rather than "1, 2, & 3", then you can simply rename them after the calculation is run. You can, of course, use other filters to drop combinations you don't like. This should get you well on your way.
lottomaniac649
Member
Hello time*treat!
On behalf of this great forum, I welcome you, the another Excel Expert to this great forum. Let the members get benefited of your expertise in Excel VBA. Your first posting seems to be really nice as far as the combinations is concerned but I think Hoareau is lacking exact explanation of his requirement.
Bye
LM649
On behalf of this great forum, I welcome you, the another Excel Expert to this great forum. Let the members get benefited of your expertise in Excel VBA. Your first posting seems to be really nice as far as the combinations is concerned but I think Hoareau is lacking exact explanation of his requirement.
Bye
LM649
time*treat
Member
engine for ranking
Option Explicit
Sub rank()
'by time*treat ~ Sun., Apr/22/2007'
Dim row As Integer, col As Integer
Dim high__val As Integer, high__col As Integer
Dim holder() As Integer, span As Integer
Dim top As Integer, best As Integer
Dim cur__val As Integer, score As Integer
Const light__yellow As Integer = 36
Const light__green As Integer = 35
top = 6: best = 3
high__col = 0: high__val = 0: span = 99: score = 0
ReDim holder(span) 'max value on sheet'
For col = 1 To span
holder(col) = 0
Next col
For col = 6 To 9
For row = 5 To 210
If Cells(row, col).Value > 0 Then 'skip blanks'
holder(Cells(row, col).Value) = holder(Cells(row, col).Value) + 1
If holder(Cells(row, col).Value) > high__val Then _
high__val = holder(Cells(row, col).Value)
If Cells(row, col).Value > high__col Then _
high__col = Cells(row, col).Value
End If
Next row
Next col
row = row + 1
For cur__val = high__val To 1 Step -1
For col = 1 To high__col
If holder(col) = cur__val Then
score = score + 1
Cells(row, score + 5).Value = col
If score <= best Then
Cells(row, score + 5).Interior.ColorIndex = light__green
Else
Cells(row, score + 5).Interior.ColorIndex = light__yellow
End If
If score = top Then
Cells.EntireColumn.AutoFit: End
End If
End If
Next col
Next cur__val
End Sub
Option Explicit
Sub rank()
'by time*treat ~ Sun., Apr/22/2007'
Dim row As Integer, col As Integer
Dim high__val As Integer, high__col As Integer
Dim holder() As Integer, span As Integer
Dim top As Integer, best As Integer
Dim cur__val As Integer, score As Integer
Const light__yellow As Integer = 36
Const light__green As Integer = 35
top = 6: best = 3
high__col = 0: high__val = 0: span = 99: score = 0
ReDim holder(span) 'max value on sheet'
For col = 1 To span
holder(col) = 0
Next col
For col = 6 To 9
For row = 5 To 210
If Cells(row, col).Value > 0 Then 'skip blanks'
holder(Cells(row, col).Value) = holder(Cells(row, col).Value) + 1
If holder(Cells(row, col).Value) > high__val Then _
high__val = holder(Cells(row, col).Value)
If Cells(row, col).Value > high__col Then _
high__col = Cells(row, col).Value
End If
Next row
Next col
row = row + 1
For cur__val = high__val To 1 Step -1
For col = 1 To high__col
If holder(col) = cur__val Then
score = score + 1
Cells(row, score + 5).Value = col
If score <= best Then
Cells(row, score + 5).Interior.ColorIndex = light__green
Else
Cells(row, score + 5).Interior.ColorIndex = light__yellow
End If
If score = top Then
Cells.EntireColumn.AutoFit: End
End If
End If
Next col
Next cur__val
End Sub
time*treat
Member
be sure to select the sheet where the numbers are.
make sure your numbers start at row 5, col F,G,H, & I to row 210, col F,G,H, & I.
(I prefer the R1C1 format.) the results appear on row 212.
make sure your numbers start at row 5, col F,G,H, & I to row 210, col F,G,H, & I.
(I prefer the R1C1 format.) the results appear on row 212.
In your macro there is only one line at the end.
I will try to explain you line by line what I want.
If I don't ,I do not want that you waste your time has to try to understand me.
Stop and thank to try
F G H I J K L M N O P O Q R S T .....
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 / 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 >
3 4 5 15 >THE RESULT 3 4 / 6 12 5 15
Explication :
there can you be 1, 2, or 3 bases according to case's
In this exemple has 6 combinations there.
the 3 first have 1,2,3 in common with which
-the 4 for the first combination is associated
-the 5 for the secondcombinaison is associated
-the 6 for the third combinaison is associated
>>>>> 1 2 / 3 4 5 6
In these 6 Numbers one finds the three combinasons the 1 and the 2 will be put in base with the 4 5 6
The fourth combination will remain similar because
In the case of the combination 1 2 7 8 which has 1 and 2
with the first 3 combinations, I prefer to have bases of three that bases of 2
>>>>>> 1 2 7 8
In the case of combinations not having any common figure they must be deferred in K L M N
the fifth and the sixth combinations in common have the 3 and the 4 (Base)
thus the remainder of the missing Numbers is put at side to find these two combinations
>>>> 3 4 / 6 12 5 15
To finish :
Columns FGHI > > the history of the arrivals which I sorted line by line
Column j > > registers "The résult" if bases were found if not nothing.
Columns KLMN > > Colonnes KLMN bases or whole combination if it are not necessary to have bases
column O = "/" if bases associated with other numbers if not nothing
From the column P all Numbers asssociés in different columns
The résult, Bases , Whole combination ,numbers associated must be on the same line.
Opposite last the combinations with bases if has there bases
in the example : opposite the last combination with 1 2 3 in base is the third combination
: opposite the last combination with 3 4 in base is the sixth combination
F G H I J K L M N O P O Q R S T .....
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 / 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 >
3 4 5 15 >THE RESULT 3 4 / 6 12 5 15
I am sorry if my explanations did not enable you to arrive earlier has this result
Thank you
I will try to explain you line by line what I want.
If I don't ,I do not want that you waste your time has to try to understand me.
Stop and thank to try
F G H I J K L M N O P O Q R S T .....
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 / 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 >
3 4 5 15 >THE RESULT 3 4 / 6 12 5 15
Explication :
there can you be 1, 2, or 3 bases according to case's
In this exemple has 6 combinations there.
the 3 first have 1,2,3 in common with which
-the 4 for the first combination is associated
-the 5 for the secondcombinaison is associated
-the 6 for the third combinaison is associated
>>>>> 1 2 / 3 4 5 6
In these 6 Numbers one finds the three combinasons the 1 and the 2 will be put in base with the 4 5 6
The fourth combination will remain similar because
In the case of the combination 1 2 7 8 which has 1 and 2
with the first 3 combinations, I prefer to have bases of three that bases of 2
>>>>>> 1 2 7 8
In the case of combinations not having any common figure they must be deferred in K L M N
the fifth and the sixth combinations in common have the 3 and the 4 (Base)
thus the remainder of the missing Numbers is put at side to find these two combinations
>>>> 3 4 / 6 12 5 15
To finish :
Columns FGHI > > the history of the arrivals which I sorted line by line
Column j > > registers "The résult" if bases were found if not nothing.
Columns KLMN > > Colonnes KLMN bases or whole combination if it are not necessary to have bases
column O = "/" if bases associated with other numbers if not nothing
From the column P all Numbers asssociés in different columns
The résult, Bases , Whole combination ,numbers associated must be on the same line.
Opposite last the combinations with bases if has there bases
in the example : opposite the last combination with 1 2 3 in base is the third combination
: opposite the last combination with 3 4 in base is the sixth combination
F G H I J K L M N O P O Q R S T .....
1 2 3 4
1 2 3 5
1 2 3 6 > THE RESULT 1 2 3 / 4 5 6
1 2 7 8 > 1 2 7 8
3 4 6 12 >
3 4 5 15 >THE RESULT 3 4 / 6 12 5 15
I am sorry if my explanations did not enable you to arrive earlier has this result
Thank you