Help setting up an office lotto pool.

[JohnPerk]

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]

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.

 

[JohnPerk]

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]

Here you go. Doesn't resolve ties thou, only the 1st erson with the most correct.

Book17
	B	C	D	E	F	G	H
2	FirstTuesday's#	22	9	10	1	59
3	FirstFriday's#	32	48	10	71	34
4	SecondTuesday's#	21	5	10	24	17
5	SecondFriday's#	33	12	10	3	25
6	ThirdTuesday's#	54	37	10	45	29
7	ThirdFriday's#	43	10	10	1	46
8	FourthTuesday's#	20	61	10	15	28
9	FourthFriday's#	30	37	10	16	14
10
11	Name	Numbers					Matches
12	BB1	25	22	16	20	3	5
13	BB2	40	6	17	10	3	3
14	BB3	1	22	11	19	18	2
15
16
17	Winner	Score
18	BB1	5
Sheet1

 

[jindon]

Hi,

try the 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]

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

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