Help setting up an office lotto pool.

JohnPerk

New Member
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
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

JohnPerk

New Member
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
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
12BB12522162035
13BB2406171033
14BB31221119182
15
16
17WinnerScore
18BB15
Sheet1

jindon

MrExcel MVP
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
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
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
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

Replies
0
Views
1K
Replies
1
Views
94
Replies
1
Views
620
Replies
2
Views
443
Replies
3
Views
126

1,186,808
Messages
5,959,938
Members
438,455
Latest member
Beverly Jarrell

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.

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

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