Generate 1X2 combinations by percentage.

[motilulla]

Using Excel 2010

Hello, I have a code which generate within 14 teams as per selection “Win=1”, “Draw=X” & “Loss=2” if we select all 3 options for 14 teams this will produce 3*3*3*3*3*3*3*3*3*3*3*3*3*3=4782969 combinations (3^14=4782969).

In this example I have selected 2 options for each teams in this case 2 options for 14 teams this will produce 2*2*2*2*2*2*2*2*2*2*2*2*2*2=16384 combinations (2^14=16384).

Here is macro do this job and generate using 2 double option for each 16384 combinations in the columns I:V till here hope explanations are clearer.


Here is my query each team has chosen 2 options for example team1 has X & 2 this mean there is 50% possibility of draw=X…or 50% possibility of loss=2…so far this has 50% chance for both either draw or loss. This VBA has designed to generate as seems.


What I want to be modified instead between 2 options X & 2 (50% chance can be vary that X play 20% and 2 play 80%) please see the % table in range for each team Y6:AA19…I am sure this will reduce the number of combinations. Please help with this if any question I will try to answer clearer.

Regards,
Moti

Generate 1X2 Combinations By Percentage.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3	Teams	Fill Bettinng 1X2				Prediction																		Teams	Fill Bettinng 1X2
4	Teams	Win	Draw	Loss		Options																		Teams	Win	Draw	Loss
5	Teams	1	X	2		S, D, T		S.N	Team1	Team2	Team3	Team4	Team5	Team6	Team7	Team8	Team9	Team10	Team11	Team12	Team13	Team14		Teams	1	X	2
6	Team1		X	2		D		1	X	1	1	X	1	1	1	X	1	X	X	1	1	X		Team1		20	80
7	Team2	1	X			D		2	X	1	1	X	1	1	1	X	1	X	X	1	1	2		Team2	80	20
8	Team3	1	X			D		3	X	1	1	X	1	1	1	X	1	X	X	1	X	X		Team3	80	20
9	Team4		X	2		D		4	X	1	1	X	1	1	1	X	1	X	X	1	X	2		Team4		20	80
10	Team5	1	X			D		5	X	1	1	X	1	1	1	X	1	X	X	X	1	X		Team5	80	20
11	Team6	1		2		D		6	X	1	1	X	1	1	1	X	1	X	X	X	1	2		Team6	80		20
12	Team7	1	X			D		7	X	1	1	X	1	1	1	X	1	X	X	X	X	X		Team7	80	20
13	Team8		X	2		D		8	X	1	1	X	1	1	1	X	1	X	X	X	X	2		Team8		80	20
14	Team9	1	X			D		9	X	1	1	X	1	1	1	X	1	X	2	1	1	X		Team9	80	20
15	Team10		X	2		D		10	X	1	1	X	1	1	1	X	1	X	2	1	1	2		Team10		20	80
16	Team11		X	2		D		11	X	1	1	X	1	1	1	X	1	X	2	1	X	X		Team11		20	80
17	Team12	1	X			D		12	X	1	1	X	1	1	1	X	1	X	2	1	X	2		Team12	80	20
18	Team13	1	X			D		13	X	1	1	X	1	1	1	X	1	X	2	X	1	X		Team13	80	20
19	Team14		X	2		D		14	X	1	1	X	1	1	1	X	1	X	2	X	1	2		Team14		20	80
20								15	X	1	1	X	1	1	1	X	1	X	2	X	X	X
21								16	X	1	1	X	1	1	1	X	1	X	2	X	X	2
22		Single	0	1				17	X	1	1	X	1	1	1	X	1	2	X	1	1	X
23		Double	14	16.384				18	X	1	1	X	1	1	1	X	1	2	X	1	1	2
24		Triple	0	1				19	X	1	1	X	1	1	1	X	1	2	X	1	X	X
25								20	X	1	1	X	1	1	1	X	1	2	X	1	X	2
26			TT:Combi	16.384				21	X	1	1	X	1	1	1	X	1	2	X	X	1	X
27								22	X	1	1	X	1	1	1	X	1	2	X	X	1	2
28								23	X	1	1	X	1	1	1	X	1	2	X	X	X	X
Generate 1X2 Combinations

Cell Formulas
Range	Formula
F6:F19	F6	=IF(COUNTA(B6:D6)=1,"S",IF(COUNTA(B6:D6)=2,"D",IF(COUNTA(B6:D6)=3,"T","")))
C22	C22	=COUNTIF(F6:F19,"S")
D22	D22	=1^C22
C23	C23	=COUNTIF(F6:F19,"D")
D23	D23	=2^C23
C24	C24	=COUNTIF(F6:F19,"T")
D24	D24	=3^C24
D26	D26	=D22*D23*D24

Option Explicit
Sub GenerateMultiple_1X2_Combinations()

    Sheets("Generate 1X2 Combinations").Select
    Range("I6:W120000").Select
    Selection.ClearContents
    Range("G6").Select
    
Dim Ans As Boolean, Col As Collection, i As Long
Dim ColSplitted() As String
Dim ind As Integer
Set Col = New Collection
Ans = BuildLoops(BuildPreLoops, ",", Col)
If Ans Then
For i = 1 To Col.Count
ColSplitted = Split(Col(i), ",")
For ind = 0 To 13
Cells(i + 5, 9 + ind).Value = ColSplitted(ind) '(i+5=Start From Row 6)Change Output Column As desire Now it is set to 9 = I
Next ind
Next i
Else
MsgBox "Error !", vbCritical
End If
End Sub
Function BuildPreLoops() As String
Dim Rnum As Integer
Dim Cnum As Integer
BuildPreLoops = ""
For Rnum = 6 To 19
BuildPreLoops = BuildPreLoops & IIf(Cells(Rnum, 2).Text <> "", Cells(Rnum, 2).Text & IIf((Cells(Rnum, 3).Text <> "" Or Cells(Rnum, 4).Text <> ""), "+", ""), "") & _
IIf(Cells(Rnum, 3).Text <> "", Cells(Rnum, 3).Text & IIf(Cells(Rnum, 4).Text <> "", "+", ""), "") & _
IIf(Cells(Rnum, 4).Text <> "", Cells(Rnum, 4).Text, "")
BuildPreLoops = BuildPreLoops & IIf(Rnum = 19, "", ", ")
Next Rnum
End Function
Function BuildLoops(ByVal St As String, Sep As String, ByRef Col As Collection) As Boolean
Dim Ar As Variant, Ar2 As Variant, ArMain As Variant
Dim i As Long, j As Long, Ctr As Long, TempSt As String
St = Application.Substitute(St, " ", "")
Ar = Split(St, Sep)
If Not IsArray(Ar) Then Exit Function
ReDim ArMain(1 To UBound(Ar) - LBound(Ar) + 1)
For i = LBound(Ar) To UBound(Ar)
Ar2 = Split(Ar(i), "+")
Ctr = Ctr + 1
ArMain(Ctr) = Ar2
Next i
For j = LBound(ArMain(1)) To UBound(ArMain(1))
TempSt = ArMain(1)(j)
BuildString 1, ArMain, Col, TempSt
Next j
BuildLoops = True
End Function
Private Function BuildString(ByRef i As Long, ByRef ArMain As Variant, ByRef Col As Collection, ByRef TempSt As String)
Dim j As Long, St As String
St = TempSt
If i < UBound(ArMain) Then
For j = LBound(ArMain(i + 1)) To UBound(ArMain(i + 1))
TempSt = St & "," & ArMain(i + 1)(j)
BuildString i + 1, ArMain, Col, TempSt
Next j
Else
Col.Add UCase(TempSt)
End If
End Function

 

[motilulla]

Hello, What I mean by % for example if I bet (1 X and 2) for any team that mean I am betting 33.33% for win, 33.33% for draw and 33.33% for in case of loss… but when I bet 1X, 12 or X2 that mean I am betting 50%, 50% for either options…and when I bet only 1 or X or 2 individual this mean 100% bet on win, draw or loss.

What I want here distribute double bet 1X, 12 or X2 instead 50%, 50%... in to 20%, 80% or 35%, 65%...for example I think team1 will win or draw so I place bet 1X…but I fell team 1 has 65% chance of winning and draw chances 35%...so programme generate for bet=1 65% combinations and for=X 35%...does it is possible?

Please help.

Regards,
Moti

 

[motilulla]

Hello, please check here is the cross post link

Generate 1X2 combinations by selected percentage.

Using Excel 2010 Hello, I have a code which generate within 14 teams as per selection (Win=1), (Draw=X) & (Loss=2) if we select all 3 options for 14 teams this will produce 3*3*3*3*3*3*3*3*3*3*3*3*3*3=4782969 combinations (3^14=4782969). In this example I have selected 2 options for each...

www.excelforum.com

Regards,
Moti

 

[Kaper]

I published the answer on excelforum, as the attachment is an important part of it. (use the link in Moti post to get there).

Or you may try to re-do my steps:

It's probably not an easy task to generate a list of unique combinations fulfilling the exact requirements.

But if you want to get close to it, and you can accept for instance equal 80.43 to 19.57 chances for each team or can accept some differences between teams read on.

I used your generator first, then moved your original table with percentages for teams (and transposed it to have the same column headers Team1, Team2, ... into range: AR3:BH6

In cell W6 used this table to find what was the probablility of such result (I6) for given team :

=INDEX(AU$4:AU$6;MATCH(I6;$AT$4:$AT$6;0))

copied this formula 14 columns right (each team) and all the way down.
then calculated a sum
AK6:

=SUM(W6:AJ6)

and generated random number AL6:

=RAND()

and copied these formulas all way down too.

Then I sorted all generated and calculated columns (H:AL) descending on the sum and (ascending, but descending woold be equally good) on random number.
The topmost row (6) has now results which were 80; 80; 80 ... probable for each of Teams

I started few rows lower the last part writing in AM111

=COUNTIF(W$6:W111;W$6)/COUNT(W$6:W111)*100

it gives a percentage of more required result for team 1 in rows 6:111
copied the formula to next 13 columns (14 teams total) then calculated average and standard deviation in columns BA and BB
copied formulas in AM:BB down and obserbved results in BA and BB.
This way I found row 475 (this row will be the same if you do it from the scratch and repeat my steps) which is a good point to start adding some of less favoreable combinations from lower rows (or may be we could treat it as a final solution)?

 

[motilulla]

Kaper, first of all lot of thanks for this enormous work, since afternoon I download the workbook I generate all combinations and filled all the formula as you per your step by step instruction thank you for detailed instruction.

My mind is giving rounds since afternoon till now 6-7 hours has passed but could not pick a thread how to filter and which combinations columns to used that passes 20% or 80% also could not understand in the AL6: =RAND() what is for?

Kaper, you have good knowledge about what I want based of that you have solved this in the better way possible. Please I want to request you need your help to understand your viewpoint I am not able to find proper filtering.

Have a nice time

My best regards,
Moti

 

[motilulla]

Hello, I got reply from @bebo021999, if i generate 5000 sets following % is generated for each team it is closer what I want but is it possible
If within 5000 attempts, the total "2" occurrences will be 4000 times, 20% 80% as i need. here below is the result and code attached.

test2_bebo021999 v1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1						Total Combi		Teams	T1	T2	T3	T4	T5	T6	T7	T8	T9	T10	T11	T12	T13	T14
2						5.000		1	0,00%	64,04%	63,54%	0,00%	64,90%	63,72%	63,56%	0,00%	63,94%	0,00%	0,00%	64,24%	64,34%	0,00%
3	Teams	Fill Bettinng 1X2				Prediction		X	36,66%	35,96%	36,46%	36,46%	35,10%	0,00%	36,44%	63,34%	36,06%	36,38%	36,46%	35,76%	35,66%	36,52%		Teams	Fill Bettinng 1X2
4	Teams	Win	Draw	Loss		Options		2	63,34%	0,00%	0,00%	63,54%	0,00%	36,28%	0,00%	36,66%	0,00%	63,62%	63,54%	0,00%	0,00%	63,48%		Teams	Win	Draw	Loss
5	Teams	1	X	2		S, D, T		S.N	Team1	Team2	Team3	Team4	Team5	Team6	Team7	Team8	Team9	Team10	Team11	Team12	Team13	Team14		Teams	1	X	2
6	Team1		X	2		D		1	2	1	1	2	1	1	1	X	1	X	2	X	1	2		Team1		20	80
7	Team2	1	X			D		2	2	1	1	X	1	1	1	X	X	2	2	1	1	X		Team2	80	20
8	Team3	1	X			D		3	X	1	1	2	1	1	1	X	1	2	2	1	1	2		Team3	80	20
9	Team4		X	2		D		4	X	X	X	X	1	2	1	2	1	2	2	1	1	X		Team4		20	80
10	Team5	1	X			D		5	2	1	1	X	1	1	X	2	1	2	2	1	1	X		Team5	80	20
11	Team6	1		2		D		6	2	1	X	2	1	1	X	X	1	2	X	1	X	2		Team6	80		20
12	Team7	1	X			D		7	X	1	1	2	X	2	1	X	1	X	2	1	1	X		Team7	80	20
13	Team8		X	2		D		8	X	1	1	X	X	1	1	2	1	2	2	X	1	2		Team8		80	20
14	Team9	1	X			D		9	2	X	X	2	1	2	1	X	1	2	X	X	1	X		Team9	80	20
15	Team10		X	2		D		10	2	X	1	X	1	1	1	X	1	2	X	1	1	2		Team10		20	80
16	Team11		X	2		D		11	2	1	1	X	X	1	1	X	1	X	2	X	X	2		Team11		20	80
17	Team12	1	X			D		12	2	X	X	2	1	1	X	X	X	2	2	X	1	2		Team12	80	20
18	Team13	1	X			D		13	2	X	1	2	X	1	X	X	1	2	2	1	1	X		Team13	80	20
19	Team14		X	2		D		14	2	1	X	2	1	1	1	X	X	X	2	1	X	2		Team14		20	80
20								15	2	X	X	X	X	1	1	X	1	2	X	X	1	2
21								16	2	1	1	2	1	2	1	2	1	2	2	X	1	2
22		Single	0	1				17	2	1	X	2	1	1	X	X	1	X	X	1	1	2
23		Double	14	16.384				18	2	X	1	2	1	1	1	X	1	2	2	1	1	2			I want combinations could be generated by their selected % for any 1 X or 2 selected option as divided 33.33% for each 1X2.
24		Triple	0	1				19	2	1	X	2	1	1	1	X	1	2	2	1	1	X
25								20	2	1	1	2	1	1	X	X	X	2	2	1	1	X
26			TT:Combi	16.384				21	2	1	1	2	1	1	1	X	1	2	2	X	X	2
27								22	2	1	1	2	1	1	1	2	1	2	X	1	1	2
28								23	X	1	1	2	X	1	1	X	1	2	2	1	1	2
29								24	2	1	1	2	1	1	1	2	1	2	2	1	1	2
30								25	2	1	X	X	1	1	1	X	1	2	X	1	1	2
31								26	2	1	1	2	1	2	1	X	1	2	2	1	1	X
32								27	2	1	1	2	1	1	1	2	X	2	2	X	X	2
33								28	X	1	1	X	1	1	1	X	1	2	2	1	1	2
34								29	2	1	X	2	1	1	1	2	X	2	2	1	1	2
35								30	2	1	1	2	1	1	1	X	1	2	X	1	1	2
36								31	2	1	1	2	X	1	1	2	1	X	X	1	1	2
37								32	2	1	1	2	1	2	X	X	1	2	2	1	1	2
Generate 1X2 Combinations

Cell Formulas
Range	Formula
F2	F2	=COUNTA(I6:I16384)
I2:V4	I2	=IF(ISERROR(COUNTIF(I$6:I$16384,$H2)/$F$2),"",COUNTIF(I$6:I$16384,$H2)/$F$2)
F6:F19	F6	=IF(COUNTA(B6:D6)=1,"S",IF(COUNTA(B6:D6)=2,"D",IF(COUNTA(B6:D6)=3,"T","")))
C22	C22	=COUNTIF(F6:F19,"S")
D22	D22	=1^C22
C23	C23	=COUNTIF(F6:F19,"D")
D23	D23	=2^C23
C24	C24	=COUNTIF(F6:F19,"T")
D24	D24	=3^C24
D26	D26	=D22*D23*D24

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I2:V4	Cell Value	=0	text	NO

'https://www.excelforum.com/excel-programming-vba-macros/1424699-generate-1x2-combinations-by-selected-percentage.html#post5956185
'Generate 1X2 combinations by selected percentage.
Option Explicit
Sub RandCombi() 'By bebo021999

Range("I6:V16384").ClearContents

Dim i&, j&, k&, t&, c&, ip, st As String
Dim rng, perce(1 To 100, 1 To 14), res()
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
rng = Range("X6:AA19").Value
For i = 1 To 14
    k = 0
    For j = 2 To 4
        If rng(i, j) <> "" Then
            For t = 1 To rng(i, j)
                k = k + 1
                perce(k, i) = IIf(j = 2, 1, IIf(j = 3, "X", 2))
            Next
        End If
    Next
Next
ip = InputBox("How many set do you want?")
If Not IsNumeric(ip) Then
    MsgBox "Invalid number"
    Exit Sub
End If
ReDim res(1 To ip, 1 To 14)
Do
    st = ""
    For i = 1 To 14
        st = IIf(i = 1, "", st & "-") & perce(WorksheetFunction.RandBetween(1, 100), i)
    Next
    If Not dic.exists(st) Then
        c = c + 1
        dic.Add st, ""
        For i = 1 To 14
            res(c, i) = Split(st, "-")(i - 1)
        Next
    End If
Loop Until c = ip
Range("I6").Resize(ip, 14).Value = res
End Sub

Please take a look if any one can help. Thank you.

Regards,
Moti

 

[motilulla]

Sorry, I forgot to provide link in the post above where is explained how the VBA code work and also can be file downloaded.

Generate 1X2 combinations by selected percentage.

Regards,
Moti

 

[motilulla]

Do any hope? for any help to suggest solution for this complicated task % generator

 

[motilulla]

Hello, Post#6 code which generate combination very closely to % but not accurate as it should be. Please help to improve for accurate %. Thank you

Regards,
Moti

 

[motilulla]

Please Help