Help please, quads,triples & pairs

[febausa]

Help Macro

Frank:
Great Job, thank you very much.

 

[Frank]

We have a saying in the Uk. You wait ages for a bus, then three come along at once..

Glad you like it febausa .

 

[jack]

Hello, frank how we can use trios and pairs blocks?
In pairs have 10 positions = 1.2, 1.3 ....... 4.5 = 10 position
* In trios have 10 positions = 1,2,3 1,2,4 ....... 3,4, 5 = 10 position
* quad = 5 = 1,2,3,4 1,2,3,4 ,.................2,3,4,5 positions= 5 position

 

[Frank]

It's up to you entirely and depends on the reason you want to know details of the drawn pairs, triples and quads. As you say, for a five ball game there are 10 combinations of pairs, 10 combinations of triples and 5 combinations of quads.


The macros list the number of times each combination has been drawn. For pairs and triples You can use auto filter to search for the maximum number of times drawn, or configure the filter for the top 5 on each page. Alternatively You may wish to configure the filter to list the pairs or triples with zero count , hence not drawn yet, you may consider these more likely to be drawn.

The information on quads is of lesser value on games with a high number of balls, the megamillions has more than a million quads, which is why the quads page only lists those which have been drawn, rather than the full list. It would take several lifetimes to draw all the mega millions quads, so in my opinion the information is if little use for that game. I just noticed that the quads page is accidentally locked, so it won't let auto filter activate, not a major bug as it turns out.

Are you one of the 74 people who downloaded the spreadsheet, Jack ? How was it for you?

 

[jack]

Hello, thank you FRANK, you can make a macro equal, but for the lottery
Ie 60/6 has 6 positions and 60 numbers
Example = 02 09 15 26 35 60
Mount twos and threes without repeating digits
* example
Pairs = 12, 56 ..... 05 24
Trios = 05 26 38 ....
* Ie there is no repetition of digits in sets, pair and trios
* Ie make a separate pairs and triplets of complete list,
Possible, because in 96% in a draw there is a trio without repetition of digits
Trios with six distinct digits

 

[Frank]

Very interesting Jack, but it's off topic.

 

[bloubul]

Hi Frank

May I ask a favor of you. You have transform some of GillesD's macro's above to fit a 5/45 PowerBall.

Will you please consider to transform these two macro's to fit GillesD_Ntups_five_balls.xlsm.

The macro's are:
Sub Résumé()
Dim sText(2) As String, nVal(4) As Integer
Application.ScreenUpdating = False
Sheets("Paires").Select
If Range("A2").Value <> "" Then
nVal(1) = Range("G1").Value
nVal(2) = Range("H1").Value
nVal(3) = Range("I1").Value
nVal(4) = Range("J1").Value
sText(1) = "For the numbers: "
sText(2) = "For the numbers: "
Range("C2").Select
Do While ActiveCell.Value <> ""
Select Case ActiveCell.Value
Case nVal(1)
sText(1) = sText(1) & ActiveCell.Offset(0, -2).Value & " / " & ActiveCell.Offset(0, -1).Value & ", "
Case nVal(3)
sText(2) = sText(2) & ActiveCell.Offset(0, -2).Value & " / " & ActiveCell.Offset(0, -1).Value & ", "
End Select
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Résumé").Select
Range("F5").Value = sText(2)
Range("F6").Value = sText(1)
End If
Sheets("Triples").Select
If Range("A2").Value <> "" Then
nVal(1) = Range("G1").Value
nVal(2) = Range("H1").Value
nVal(3) = Range("I1").Value
nVal(4) = Range("J1").Value
sText(2) = "For the numbers: "
Range("D2").Select
Do While ActiveCell.Value <> ""
Select Case ActiveCell.Value
Case nVal(4)
sText(2) = sText(2) & ActiveCell.Offset(0, -3).Value & " / " & ActiveCell.Offset(0, -2).Value & " / " & ActiveCell.Offset(0, -1).Value & ", "
End Select
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Résumé").Select
Range("F10").Value = sText(2)
End If
Sheets("Quads").Select
If Range("A2").Value <> "" Then
nVal(1) = Range("H1").Value
nVal(2) = Range("I1").Value
sText(1) = "For the numbers: "
Range("E2").Select
Do While ActiveCell.Value <> ""
Select Case ActiveCell.Value
Case nVal(1)
sText(1) = sText(1) & ActiveCell.Offset(0, -4).Value & " / " & ActiveCell.Offset(0, -3).Value & " / " & ActiveCell.Offset(0, -2).Value & " / " & ActiveCell.Offset(0, -1).Value & ", "
End Select
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Résumé").Select
Range("F15").Value = sText(1)
End If

Sub Absences()
Dim A As Integer, B As Integer, C As Integer, I As Integer, nDraws As Integer
Dim nPair(49, 49) As Integer, nTriple(49, 49, 49) As Integer
Application.ScreenUpdating = False
Sheets("Data").Select
Range("A1").Select
nDraws = Range("L1").Value
For A = 1 To 48
For B = A + 1 To 49
nPair(A, B) = 0
Next B
Next A
For A = 1 To 47
For B = A + 1 To 48
For C = B + 1 To 49
nTriple(A, B, C) = 0
Next C
Next B
Next A
For I = 1 To nDraws
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 6).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 6).Value) = I
nPair(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 6).Value) = I
nPair(ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 6).Value) = I
nPair(ActiveCell.Offset(I, 5).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 5).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 5).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value, ActiveCell.Offset(I, 6).Value) = I
nTriple(ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value, ActiveCell.Offset(I, 6).Value) = I
Next I
Sheets("Paires").Select
Range("A1").Select
I = 1
For A = 1 To 48
For B = A + 1 To 49
ActiveCell.Offset(I, 3).Value = nDraws - nPair(A, B)
I = I + 1
Next B
Next A
Sheets("Triples").Select
Range("A1").Select
I = 1
For A = 1 To 47
For B = A + 1 To 48
For C = B + 1 To 49
ActiveCell.Offset(I, 4).Value = nDraws - nTriple(A, B, C)
I = I + 1
Next C
Next B
Next A
End Sub


BlouBul

 

[Frank]

Hi Bloubul, I'll take a look, but your first sub doesn't have an End Sub. Can I assume it comes after the End If just before the next sub starts?

I might have a problem with Résumé as I don't have a French keyboard, so I can't name a sheet Résumé unless character map lets me.

Meanwhile I'm trying to figure out what they do before I start

 

[Frank]

Also why is Resume macro examining the contents of cells G1,H1,I1,J1 on the pairs sheet and triples sheet, when they are always empty ? I just run it with minimal amendment and it just generated huge strings such as:-
For the numbers: 1 / 2 / 3, 1 / 2 / 4, 1 / 2 / 5, 1 / 2 / 8, 1 / 2 / 11, 1 / 2 / 12, 1 / 2 / 13, 1 / 2 / 14, 1 / 2 / 16, 1 / 2 / 21, 1 / 2 / 22, 1 / 2 / 23, 1 / 2 / 24, 1 / 2 / 25, 1 / 2 / 26, 1 / 2 / 27, 1 / 2 / 28, 1 / 2 / 29, 1 / 2 / 30, 1 / 2 / 31, 1 / 2 / 32, 1 / 2 / 33, 1 / 2 / 34, 1 / 2 / 35, 1 / 2 / 36, 1 / 2 / 38, 1 / 2 / 40, 1 / 2 / 41, 1 / 2 / 42, 1 / 2 / 44, 1 / 2 / 46, 1 / 2 / 47, 1 / 2 / 50, 1 / 2 / 51, 1 / 2 / 52, 1 / 2 / 53, 1 / 2 / 54, 1 / 2 / 56, 1 / 2 / 57, 1 / 2 / 58, 1 / 2 / 59, 1 / 3 / 5, 1 / 3 / 6, 1 / 3 / 12, 1 / 3 etc..... 33000 characters !!
its just a readout of the line by line results, but in a very reader unfriendly format ...

Whats the point ? or has this gone wrong ?

 

[Frank]

The Absence Macro will only work if:-

Using the downloaded GillesD ntuplets spreadsheet, Column A on the data sheet contains DRAW NUMBERS and Draw 1 is at the top (row 2) and the numbers increase down the sheet.

Note the macro needs to read the number of draws from the Data sheet. To do this you need a cell on the data sheet out of the way, say cell S1, which contains the highest draw number. I used the formula =MAX(A:A) in that cell. I then named the cell "Numdraws" (using the name manager). The macro looks for this named cell, and wont work if it doesn't find it.

Here is the Absence macro, paste it on the same module as the other macros.:-

Sub Absences()

H = Range("HighestBallValue").Value
Dim A As Integer, B As Integer, C As Integer, I As Long, nDraws As Integer
ReDim nPair(H, H) As Integer, nTriple(H, H, H) As Integer
Application.ScreenUpdating = False
Sheets("Data").Select
Range("A1").Select
nDraws = Range("numdraws").Value

For A = 1 To H - 1
For B = A + 1 To H
nPair(A, B) = 0
Next B

Next A

For A = 1 To H - 2
For B = A + 1 To H - 1
For C = B + 1 To H
nTriple(A, B, C) = 0
Next C
Next B
Next A

For I = 1 To nDraws
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nPair(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nPair(ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I

nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 1).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 2).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I
nTriple(ActiveCell.Offset(I, 3).Value, ActiveCell.Offset(I, 4).Value, ActiveCell.Offset(I, 5).Value) = I


Next I


Sheets("Pairs").Select
Range("A1").Select
I = 1
For A = 1 To H - 1
For B = A + 1 To H
ActiveCell.Offset(I, 3).Value = nDraws - nPair(A, B)
I = I + 1
Next B
Next A

Sheets("Triples").Select
Range("A1").Select

I = 1
For A = 1 To H - 2
For B = A + 1 To H - 1
For C = B + 1 To H
ActiveCell.Offset(I, 4).Value = nDraws - nTriple(A, B, C)
I = I + 1
Next C
Next B
Next A
End Sub

That should do it for all 5 ball games, High ball numbered games with lots of draws will take longer to run.

The jury is out about the Resume macro, I don't understand why its there!

 

[Frank]

This forum is soooo frustrating with its restrictions! I spotted a mistake, but it was too late to edit.... You can't put your formula anywhere you like because the sheet is locked. I'm not preparing another sheet to download, or issuing a password so this is it.

Your only options are the bottom (as far down as possible) of column C. (stay away from columns A and F). I used Find >goto and typed in C1000000 to find my cell. (Excel 2007 and later) then typed in my formula =Max(A:A). Then named the cell "Numdraws".

Good luck!

 

[Frank]

After all that, you would need to change the delete macro, because you've added a new column of results to delete, (but don't delete the cell you put the =Max(A:A) formula in, so stop a line above it) and to run the absence macro from the button, you need to change the runAll macro to include it.

Sub DeleteOldresults()

Sheets(Array("pairs", "triples", "quads")).Select
Range("A2:F999999").Select
Selection.ClearContents
End Sub

Sub runAll()

DeleteOldresults
Pairs
Triples
Quads
Absences

End Sub

Gets tricky when you move the goalposts after the event, doesn't it ?

 

[bloubul]

Hi Frank

Many thanks for your help.
I'll will give it a go

BlouBul