finding a common set of numbers in separate rows

[Spiro]

In order to win a prize in the lottery (6/49) I need to get at least 3
of the drawn numbers. If I betted on the same three numbers in each
draw I might at the very least get some of my money back.

Does anyone know how to find the three most common set of numbers in
the draws made in a Lottery?


For example, if we were to run such a macro in the following table,
with the results of each draw in its own row:

A B C D E F
1 2 4 13 21 27 47
2 8 10 16 17 30 47
3 4 16 21 27 35 43
4 3 11 25 28 34 45
5 4 8 21 27 39 44


The result would be:

4 21 27

Anyone with a macro that can do this?

 

[Chris Leonard]

Spiro.

I once had a theory ....

If I randomly generated one hundred lines for each week over a four week
period although it would cost me £400 I would, I was sure, at least break
even, I mean, wouldn't you think that in 400 lines you get a few 4's and a
few 3's at least ..... enough to as I say break even.

Why not have a go using the last 4 weeks results I thought (I'm not brave
enough to walk into the newsagent with £400! - it's not the loosing £400
it's telling the wife how I lost it!!)

Anyway, I developed a nice little macro in Excel to generate me 400 unique
lines and check the numbers of the last 4 weeks against 4 sets of 100.

I'm pleased it's as far as I got .....

Net loss would have been £320! Just to make sure I went back over a few
months, only once did I get into the £300's never made it over £400.

Save you money mate!!

Chris

 

[Guest]

Spiro

The following will give a list of the numbers in the range and a count of their appearances. It outputs the results starting in H1 and shows all the numbers sorted in descending order of count. In your example there are 3 numbers with the same count, but having all numbers output will let you see if there are more with the same count.

The array containing the number and the count is fixed. I got lazy. You can either overdimension (as I have) or make the array dynamic (redim).

Tony

Sub bbb()
Dim arr(50, 2)
Dim uniq As New Collection
'get a list of the unique numbers
On Error Resume Next
For Each ce In Range("a1:f5")
uniq.Add ce, Str$(ce)
Next ce
On Error GoTo 0

'build an array of the numbers and their counts
For i = 1 To uniq.Count
arr(i, 1) = uniq(i)
arr(i, 2) = WorksheetFunction.CountIf(Range("a1:f5"), uniq(i))
Next i

'sort the results
For i = 1 To uniq.Count - 1
For j = i + 1 To uniq.Count
If arr(j, 2) > arr(i, 2) Then
holder = arr(i, 1)
holder2 = arr(i, 2)
arr(i, 1) = arr(j, 1)
arr(i, 2) = arr(j, 2)
arr(j, 1) = holder
arr(j, 2) = holder2
End If
Next j
Next i

'output the results
Range("h1").Select
For i = 1 To uniq.Count
ActiveCell.Value = arr(i, 1)
ActiveCell.Offset(0, 1).Value = arr(i, 2)
ActiveCell.Offset(1, 0).Select
Next i

End Sub


----- Spiro wrote: -----

In order to win a prize in the lottery (6/49) I need to get at least 3
of the drawn numbers. If I betted on the same three numbers in each
draw I might at the very least get some of my money back.

Does anyone know how to find the three most common set of numbers in
the draws made in a Lottery?


For example, if we were to run such a macro in the following table,
with the results of each draw in its own row:

A B C D E F
1 2 4 13 21 27 47
2 8 10 16 17 30 47
3 4 16 21 27 35 43
4 3 11 25 28 34 45
5 4 8 21 27 39 44


The result would be:

4 21 27

Anyone with a macro that can do this?