Help setting up an office lotto pool.

JohnPerk

New Member
Joined
Jan 21, 2005
Messages
12
What I am trying to do is set up a pool at my job where everyone buy's lotto tickets. The numbers from those tickets get put into one sheet. Then when the lottery numbers come out twice a week those numbers will be matched up against the numbers on there tickets automatically. I already have the sheet set up, all I need is the formulas that will allow me to (A.)first figure out how many numbers each person's ticket matched progressively throughout the whole month. That would mean that there are 8 sets of 5 numbers throughout the month (40 numbers)that have to be checked on every persons set of 5 numbers. AND (B.) second returt those people who are in the lead. If you could help me I would graetly appreciate it. If you need more info, or you would like to see what I have so far please let me know. I would really like to get this thing started. Also this is my first post, so if I did anything wrong I am sorry. Thank you for all your help. JOHN PERK
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
For conditional formatting, select cell C12, then click Format|Conditional Formatting. A dialog box appears. In the first entry box, you'll see Cell Value Is, change this to Fomrula Is using the drop down arrow. Then place this in the box to the right: =COUNTIF($C$2:G$9,C12)>0. Finally, click the little format box and select how you'd like to have the cells hilited, maybe red or green background (pattern tab). Then click OK. Now copy and paste special formats to the other cells, C12:G14.

As far as IDing the person in the lead, there are many ways to do this. You could try:

=INDEX(B12:B14,MATCH(MAX(I12:I14),I12:I14,0))

but this doesn't handle ties. If you don't care then fine. You could also use conditional formatting to hilite the leader's name. In this case, if there was a tie, you could have more than one name hilited. To do this, you need a Formula Is conditional Format with the formula =I12=MAX($I$12:$I$14).

Hope this gets you where you're looking to go.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

JohnPerk

New Member
Joined
Jan 21, 2005
Messages
12
Then Could I retrieve all the highlighted names along with the high score. You have been such a help to me I don't know how I could ever repay you. Thank you very much
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Here you go. Doesn't resolve ties thou, only the 1st erson with the most correct.
Book17
BCDEFGH
2FirstTuesday's#22910159
3FirstFriday's#3248107134
4SecondTuesday's#215102417
5SecondFriday's#331210325
6ThirdTuesday's#5437104529
7ThirdFriday's#431010146
8FourthTuesday's#2061101528
9FourthFriday's#3037101614
10
11NameNumbersMatches
12BB12522162035
13BB2406171033
14BB31221119182
15
16
17WinnerScore
18BB15
Sheet1
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

try the code

Code:
Sub lotto()
Dim dic As Object, a, y, z, rng As Range, x As Range
Set dic = CreateObject("scripting.dictionary")
Set rng = Application.InputBox("Select range you wnat to compare", "Check", Type:=8)
With ActiveSheet
    If rng.Rows.Count <> 1 Or rng.Columns.Count <> 5 Then
        MsgBox "Improper range selection!", vbExclamation
        GoTo Last
    End If
    For Each x In .Range("c2:g9")
        If Not dic.exists(x.Value) Then
            dic.Add x.Value, Nothing
        End If
    Next
    For Each x In rng
        If dic.exists(x.Value) Then
            z = z + 1
            y = y & x.Value & ", "
        End If
    Next
    a = z & " matches " & Left(y, Len(y) - 2)
    .Range("i" & rng.Row).Value = a
End With
Last:
Set dic = Nothing
End Sub

hope this helps
jindon
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,
I have converted my code to UDF
use it like

=LOTTO(range1,range2,True or False)

where:
range1: Range that has resuts i.e C2:G9
range2: Range that has selected #s i.e. C12:G12
True or False: Default setting is False displays only count of matches
If it's set to True or 1 then displays count of matches as well as the matching #s
Code:
Function LOTTO(r As Range, rng As Range, Optional d As Boolean = False) As Variant
Dim dic As Object, a, y, z, x As Range
Set dic = CreateObject("scripting.dictionary")
If r Is Nothing Or rng Is Nothing Then GoTo Last
    If rng.Rows.Count <> 1 Or rng.Columns.Count <> 5 Then
        GoTo Last
    End If
    For Each x In r
        If Not dic.exists(x.Value) Then
            dic.Add x.Value, Nothing
        End If
    Next
    For Each x In rng
        If dic.exists(x.Value) Then
            z = z + 1
            y = y & x.Value & ", "
        End If
    Next
    If d = False Then
        a = z
    Else
        a = z & " matches " & Left(y, Len(y) - 2)
    End If
    LOTTO = a
Last:
Set dic = Nothing
End Function
hope this helps

jindon
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,579
Messages
5,765,211
Members
425,265
Latest member
bishopc22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top