Tricky combinations generate using following distribution

Kishan · Oct 26, 2016

Using Excel 2000

Hi,

Formula column M shows the TT: Cobinationes could be made with each distribution as per C:K columns

Book1

C

D

E

F

G

H

I

J

K

L

M

N

5

1|1

1|X

X|1

1|2

2|1

X|2

2|X

X|X

2|2

Sum

TT:Combinations

6

2

1

3

0

1

0

7

420

7

3

0

3

0

1

0

7

140

8

1

3

0

7

140

9

0

3

0

4

0

7

35

10

0

5

0

2

0

7

21

11

0

5

1

0

1

7

42

12

0

1

2

1

0

7

1.260

13

0

1

2

3

0

1

0

7

420

Sheet1

Cell Formulas
Range	Formula
M6	=SUM(C6:K6)
M7	=SUM(C7:K7)
M8	=SUM(C8:K8)
M9	=SUM(C9:K9)
M10	=SUM(C10:K10)
M11	=SUM(C11:K11)
M12	=SUM(C12:K12)
M13	=SUM(C13:K13)
N6	{=FACT(SUM(C6:K6))/PRODUCT(FACT(C6:K6))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I need VBA, which can generate 420 unique combinations if I use following distribution in cells K6:S6 distribution length is 9 and sum will be always = 7

Does it is possible to get combinations with length 7 in columns C:I, only unique = 420 the example below shown just few dummy combinations in the columns C:I

Example…

Book1

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

2

3

4

5

P1

P2

P3

P4

P5

P6

P7

1|1

1|X

X|1

1|2

2|1

X|2

2|X

X|X

2|2

TT:Combinations

6

1|1

1|X

2|1

X|1

2

1

3

0

1

0

420

7

1|1

1|X

X|1

2|1

X|1

8

1|1

1|X

X|1

2|1

X|1

9

1|1

1|X

X|1

2|1

10

1|1

2|1

X|1

1|X

11

1|1

2|1

1|X

X|1

12

1|1

2|1

X|1

1|X

X|1

13

1|1

2|1

X|1

1|X

X|1

14

1|1

X|1

2|1

X|1

1|X

15

1|1

X|1

1|X

X|1

2|1

X|1

16

1|1

X|1

1|X

2|1

17

1|1

X|1

2|1

1|X

X|1

18

1|1

X|1

2|1

1|X

X|1

19

1|1

X|1

2|1

1|X

20

1|1

X|1

2|1

X|1

1|X

21

1|1

X|1

2|1

X|1

1|X

X|1

22

1|1

X|1

1|X

X|1

2|1

23

1|1

X|1

1|X

X|1

2|1

24

1|1

X|1

1|X

2|1

X|1

25

1|1

X|1

1|X

2|1

X|1

26

1|1

1|X

1|1

X|1

2|1

X|1

27

1|1

1|X

1|1

X|1

2|1

28

1|1

1|X

1|1

2|1

X|1

29

1|1

1|X

1|1

X|1

2|1

X|1

30

1|1

1|X

2|1

X|1

1|1

X|1

31

1|1

1|X

2|1

X|1

1|1

X|1

32

1|1

1|X

2|1

X|1

1|1

33

1|1

1|X

2|1

1|1

X|1

Sheet2

Thank you in advance

Regards,
Kishan

Kishan · Oct 29, 2016

Hi,

Ok It come in my mind what if I change post#1 layout like follow does it is variable?

Instead putting numbers of time to be used each pattern in row K6:S6. Put them in columns A:I times to repeated each pattern

2-Pattern 1|1 in column A
1-Pattern 1|X in column B
3-Pattern X|1 in column C
1-Pattern 2|1 in column D

And create 420 sets out of total 7 Patterns in 7 columns as shown in the example below

Example shows sample output total to be generated 420 combinations or may be it called permutations

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

Set 1

Set 2

Set 3

Set 4

Set 5

Set 6

Set 7

Set 8

Set 9

P1

P2

P3

P4

P5

P6

P7

2

1|1

1|X

X|1

2|1

1|1

1|X

2|1

X|1

3

1|1

X|1

1|1

1|X

X|1

2|1

X|1

4

X|1

1|1

1|X

X|1

2|1

X|1

5

1|1

1|X

X|1

2|1

6

1|1

2|1

X|1

1|X

7

1|1

2|1

1|X

X|1

8

1|1

2|1

X|1

1|X

X|1

9

1|1

2|1

X|1

1|X

X|1

10

1|1

X|1

2|1

X|1

1|X

11

1|1

X|1

1|X

X|1

2|1

X|1

12

1|1

X|1

1|X

2|1

13

1|1

X|1

2|1

1|X

X|1

14

1|1

X|1

2|1

1|X

X|1

15

1|1

X|1

2|1

1|X

16

1|1

X|1

2|1

X|1

1|X

17

1|1

X|1

2|1

X|1

1|X

X|1

18

1|1

X|1

1|X

X|1

2|1

19

1|1

X|1

1|X

X|1

2|1

20

1|1

X|1

1|X

2|1

X|1

21

1|1

X|1

1|X

2|1

X|1

22

1|1

1|X

1|1

X|1

2|1

X|1

23

1|1

1|X

1|1

X|1

2|1

24

1|1

1|X

1|1

2|1

X|1

25

1|1

1|X

1|1

X|1

2|1

X|1

26

1|1

1|X

2|1

X|1

1|1

X|1

27

1|1

1|X

2|1

X|1

1|1

X|1

28

1|1

1|X

2|1

X|1

1|1

29

1|1

1|X

2|1

1|1

X|1

30

1|1

1|X

X|1

2|1

1|1

31

1|1

1|X

X|1

2|1

X|1

1|1

32

1|1

1|X

X|1

1|1

2|1

X|1

33

1|1

1|X

X|1

2|1

X|1

1|1

X|1

34

1|1

1|X

X|1

1|1

X|1

2|1

X|1

35

1|1

1|X

X|1

1|1

2|1

36

1|1

1|X

X|1

2|1

1|1

X|1

37

1|1

1|X

X|1

1|1

2|1

X|1

38

1|1

1|X

X|1

2|1

X|1

1|1

39

1|1

1|X

X|1

1|1

X|1

2|1

40

1|1

1|X

X|1

2|1

1|1

X|1

41

1|1

1|X

X|1

1|1

X|1

2|1

42

1|1

2|1

1|1

X|1

1|X

X|1

43

1|1

2|1

1|1

1|X

X|1

44

1|1

2|1

1|1

X|1

1|X

X|1

45

1|1

2|1

1|1

X|1

1|X

46

1|1

2|1

1|X

X|1

1|1

47

1|1

2|1

1|X

1|1

X|1

48

1|1

2|1

1|X

X|1

1|1

X|1

49

1|1

2|1

1|X

X|1

1|1

X|1

50

1|1

2|1

X|1

1|1

1|X

X|1

51

1|1

2|1

X|1

1|1

X|1

1|X

52

1|1

2|1

X|1

1|X

1|1

53

1|1

2|1

X|1

1|1

1|X

X|1

54

1|1

2|1

X|1

1|X

X|1

1|1

55

1|1

2|1

X|1

1|X

1|1

X|1

56

1|1

2|1

X|1

1|1

X|1

1|X

57

1|1

2|1

X|1

1|X

X|1

1|1

X|1

58

1|1

2|1

X|1

1|1

X|1

1|X

X|1

59

1|1

2|1

X|1

1|X

X|1

1|1

60

1|1

2|1

X|1

1|X

1|1

X|1

61

1|1

2|1

X|1

1|1

1|X

62

1|1

X|1

1|1

2|1

X|1

1|X

63

1|1

X|1

1|1

1|X

X|1

2|1

64

1|1

X|1

1|1

2|1

1|X

X|1

65

1|1

X|1

1|1

X|1

1|X

X|1

2|1

66

1|1

X|1

1|1

X|1

2|1

1|X

X|1

67

1|1

X|1

1|1

X|1

2|1

X|1

1|X

68

1|1

X|1

1|1

X|1

1|X

2|1

X|1

69

1|1

X|1

1|1

2|1

X|1

1|X

X|1

70

1|1

X|1

1|1

1|X

2|1

X|1

71

1|1

X|1

1|1

1|X

X|1

2|1

X|1

72

1|1

X|1

1|1

X|1

1|X

2|1

73

1|1

X|1

1|1

X|1

2|1

1|X

74

1|1

X|1

1|X

2|1

X|1

1|1

X|1

75

1|1

X|1

1|X

2|1

X|1

1|1

76

1|1

X|1

1|X

1|1

X|1

2|1

X|1

77

1|1

X|1

1|X

X|1

2|1

1|1

78

1|1

X|1

1|X

X|1

1|1

2|1

X|1

79

1|1

X|1

1|X

X|1

1|1

2|1

80

1|1

X|1

1|X

2|1

1|1

X|1

81

1|1

X|1

1|X

1|1

X|1

2|1

82

1|1

X|1

1|X

1|1

2|1

X|1

83

1|1

X|1

1|X

X|1

2|1

1|1

X|1

84

1|1

X|1

1|X

X|1

2|1

X|1

1|1

85

1|1

X|1

1|X

X|1

1|1

X|1

2|1

86

1|1

X|1

2|1

1|1

X|1

1|X

87

1|1

X|1

2|1

1|X

X|1

1|1

X|1

88

1|1

X|1

2|1

1|X

X|1

1|1

89

1|1

X|1

2|1

X|1

1|1

1|X

X|1

90

1|1

X|1

2|1

1|1

1|X

X|1

91

1|1

X|1

2|1

X|1

1|X

X|1

1|1

92

1|1

X|1

2|1

1|X

1|1

X|1

93

1|1

X|1

2|1

1|1

X|1

1|X

X|1

94

1|1

X|1

2|1

X|1

1|X

1|1

X|1

95

1|1

X|1

2|1

X|1

1|1

X|1

1|X

96

1|1

X|1

2|1

X|1

1|1

1|X

97

1|1

X|1

2|1

X|1

1|X

1|1

98

1|1

X|1

1|1

X|1

2|1

1|X

99

1|1

X|1

2|1

1|1

1|X

100

1|1

X|1

1|X

2|1

1|1

X|1

Sheet3

Thank you

Regards,
Kishan

Kishan · Oct 29, 2016

B___P · Oct 29, 2016

Hi Kishan,
this is your most challenging post. Looks simple but permutations with repetitions are not so easy to get as they seem.
Use this layout:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1\|1	1\|X	X\|1	1\|2	2\|1	X\|2	2\|X	X\|X	2\|2		P1	P2	P3	P4	P5	P6	P7
2	2		3		1	1

Foglio7

In row 2 enter presences of couples in a game ticket under the obvious constrain they sum to 7 and put in a module this code:

Code:

Option Explicit
Sub PlaySlip_Peremutations()
Dim mystring As String
Dim idx2 As Long
Dim idx4 As Long
Dim Count As Long
Dim Count2 As Long
Dim Count3 As Long
Dim PermutationsArr()
Dim StartP As Long
Dim EndP As Long
Dim idxArr As Long
Dim idxLowPer As Long
Dim MyChar As String

mystring = "!!!!!!!"
For idx2 = 1 To 9
    If Cells(2, idx2).Value > 0 Then
        mystring = Replace(mystring, "!", Cells(2, idx2).Column, 1, Cells(2, idx2).Value)
    End If
Next idx2

Cells(1, 10).Activate
ReDim PermutationsArr(1 To Fact(Len(mystring)))
For Count = 1 To UBound(PermutationsArr)
    PermutationsArr(Count) = mystring
Next Count

For StartP = 1 To Len(mystring)
    idxArr = 0
    Do While idxArr < UBound(PermutationsArr)
        For EndP = StartP To Len(mystring)
            For Count = 1 To Fact(Len(mystring) - StartP + 1) / (Len(mystring) - StartP + 1)
                idxArr = idxArr + 1
                MyChar = Mid(PermutationsArr(idxArr), StartP, 1)
                Mid(PermutationsArr(idxArr), StartP) = Mid(PermutationsArr(idxArr), EndP, 1)
                Mid(PermutationsArr(idxArr), EndP) = MyChar
            Next Count
        Next EndP
    Loop
Next StartP

For idxArr = 1 To UBound(PermutationsArr)
    idxLowPer = idxArr
    For Count = idxArr To UBound(PermutationsArr)
        If PermutationsArr(Count) < PermutationsArr(idxLowPer) Then
            idxLowPer = Count
        End If
    Next Count
    MyChar = PermutationsArr(idxArr)
    PermutationsArr(idxArr) = PermutationsArr(idxLowPer)
    PermutationsArr(idxLowPer) = MyChar
Next idxArr

MyChar = "": Count2 = 0: Count3 = 1
For Count = 1 To UBound(PermutationsArr)
    If PermutationsArr(Count) <> MyChar Then
        Count2 = Count2 + 1: Count3 = Count3 + 1: MyChar = PermutationsArr(Count)
        For idx4 = 1 To 7
        Cells(Count3, 10 + idx4).Value = Cells(1, CLng(Mid(PermutationsArr(Count), idx4, 1))).Value
        Next idx4
    End If
Next Count

End Sub
Public Function Fact(i As Integer) As Long
Dim k As Integer
Fact = 1
For k = i To 2 Step -1
    Fact = Fact * k
Next k
End Function

At the moment I am playing lotto games, the one you like in Italy is named Totocalcio and was my favourite many years ago so I must think a bit to remember all the tricks. I was wondering why you use couples, once when totocalcio was the most popular game and set on 13 games (that's the reason why in my country 13 is a lucky number!), the most common result was six 1, five X, and two 2.

Have a nice weekend.

Kishan · Oct 29, 2016

B___P said:
Hi Kishan,
this is your most challenging post. Looks simple but permutations with repetitions are not so easy to get as they seem.

Hi, B__P, first of all I want to thank you very much for giving a great solution. Really I needed it most

Challenging yes it was because this were not normal, it were extraordinary query after I lounge the question I did 9 times bump in 10^th time I change the view of question 11^th time did bump and get the answer from you after the page has visited 340+ times this say much about the complicity of the question.

B___P said:
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q

1
1|1
1|X
X|1
1|2
2|1
X|2
2|X
X|X
2|2
P1

P2
P3
P4
P5
P6
P7

2
2
3
1
1

<tbody>
</tbody>
Foglio7

In row 2 enter presences of couples in a game ticket under the obvious constrain they sum to 7 .

I tested the code combining much combination which sum reach to 7 and find out possible combinations could be formed by the sequence using your Formula you provide me in other post also were difficult too. This says you accept the challenges. Proof is this is done

B___P said:

Code:

Option Explicit
Sub PlaySlip_Peremutations()
Dim mystring As String
Dim idx2 As Long
Dim idx4 As Long
Dim Count As Long
Dim Count2 As Long
Dim Count3 As Long
Dim PermutationsArr()
Dim StartP As Long
Dim EndP As Long
Dim idxArr As Long
Dim idxLowPer As Long
Dim MyChar As String

mystring = "!!!!!!!"
For idx2 = 1 To 9
    If Cells(2, idx2).Value > 0 Then
        mystring = Replace(mystring, "!", Cells(2, idx2).Column, 1, Cells(2, idx2).Value)
    End If
Next idx2

Cells(1, 10).Activate
ReDim PermutationsArr(1 To Fact(Len(mystring)))
For Count = 1 To UBound(PermutationsArr)
    PermutationsArr(Count) = mystring
Next Count

For StartP = 1 To Len(mystring)
    idxArr = 0
    Do While idxArr < UBound(PermutationsArr)
        For EndP = StartP To Len(mystring)
            For Count = 1 To Fact(Len(mystring) - StartP + 1) / (Len(mystring) - StartP + 1)
                idxArr = idxArr + 1
                MyChar = Mid(PermutationsArr(idxArr), StartP, 1)
                Mid(PermutationsArr(idxArr), StartP) = Mid(PermutationsArr(idxArr), EndP, 1)
                Mid(PermutationsArr(idxArr), EndP) = MyChar
            Next Count
        Next EndP
    Loop
Next StartP

For idxArr = 1 To UBound(PermutationsArr)
    idxLowPer = idxArr
    For Count = idxArr To UBound(PermutationsArr)
        If PermutationsArr(Count) < PermutationsArr(idxLowPer) Then
            idxLowPer = Count
        End If
    Next Count
    MyChar = PermutationsArr(idxArr)
    PermutationsArr(idxArr) = PermutationsArr(idxLowPer)
    PermutationsArr(idxLowPer) = MyChar
Next idxArr

MyChar = "": Count2 = 0: Count3 = 1
For Count = 1 To UBound(PermutationsArr)
    If PermutationsArr(Count) <> MyChar Then
        Count2 = Count2 + 1: Count3 = Count3 + 1: MyChar = PermutationsArr(Count)
        For idx4 = 1 To 7
        Cells(Count3, 10 + idx4).Value = Cells(1, CLng(Mid(PermutationsArr(Count), idx4, 1))).Value
        Next idx4
    End If
Next Count

End Sub
Public Function Fact(i As Integer) As Long
Dim k As Integer
Fact = 1
For k = i To 2 Step -1
    Fact = Fact * k
Next k
End Function

At the moment I am playing lotto games, the one you like in Italy is named Totocalcio and was my favourite many years ago so I must think a bit to remember all the tricks. I was wondering why you use couples, once when totocalcio was the most popular game and set on 13 games (that's the reason why in my country 13 is a lucky number!), the most common result was six 1, five X, and two 2.

Have a nice weekend.

Does the Totocalcio is football game. Is still continuing there. I am playing football and I find will be more effective to analyse the couple’s instead individual sign I hope I will achieve with your help out with some interesting results. Let see.

I'm grateful for your help! I love your coding it is fine and precise

You have a nice weekend too.

Kind Regards,
Kishan

Tricky combinations generate using following distribution

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

B___P

Active Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

Tricky combinations generate using following distribution

Kishan

Well-known Member

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

B___P

Active Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock