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

 

[JohnPerk]

This was a formula that I got on some web site, but it only works with one day's worth of numbers not 8. I tried changing it, but the workseet is locked and when I try it gives me a #VALUE! error reading =IF(SUM(C16:G16)=0,0,SUM(IF(C16=$C$4:$G$4,1,0)+IF(D16=$C$4:$G$4,1,0)+IF(E16=$C$4:$G$4,1,0)+IF(F16=$C$4:$G$4,1,0)+IF(G16=$C$4:$G$4,1,0)))

 

[Harvey]

well.. it is at least as long as my formula

 

[JohnPerk]

How about this can I have a formula that highlights any of the 5 numbers that match up with the top 40 numbers. I know the formula for counting highlighted spaces. Do you think this will work?

 

[Seti]

Try this formual for matches:

=SUMPRODUCT((C12=$C$2:$G$9)+(D12=$C$2:$G$9)+(E12=$C$2:$G$9)+(F12=$C$2:$G$9)+(G12=$C$2:$G$9))

 

[Seti]

To hilite the numbers in C12:G12 that match the table above, use conditional formatting, Formula Is, =COUNTIF($C$2:G$9,C12)>0 for cell C12 and then paint the format over the rest of the cells you want highlighted. Select the appropriate hiliting from the format menu in conditional formatting.

 

[JohnPerk]

Sorry Seti, I am not comprehending, what you are saying with the hilighting, and that formula before mentioned didn't work, it still returned multiple matches when the same # showed up.

 

[Seti]

John,

My first post is a formula to determine how many of the numbers in C12:G12 are in the table above. In my simulation, it worked just fine. If it doesn't work for you, let me know what you think is wrong with it. [edit: I see the probelm now working on it.]

My second post was an attempt to get each number in the players list (C12:G12) to be come hilited, maybe the number is made red and bold or the back ground turns green (like money?). This approcah uses conditional formatting. If you are not familiar with it, post back.

I deleted my sample of your spreadsheet. I will recreate and post it back to see if that helps you out.

 

[JohnPerk]

OK here is what I wanted. I wanted when one of those #'s (C12:G12) comes up in the table above for it to be hilighted in yellow. My question for you is ( I am not really familiar with conditional formatting) where do I put that Formula (=COUNTIF($C$2:G$9,C12)>0) . What I did in the conditional formatting box was put Cell Value is Equal to =COUNTIF($C$2:G$9,C12)>0... Is that right or am I losing my mind, cuz it doesn't seem to be working for me. Sorry to bother you so much, but I appreciate all the help....

 

[Seti]

OK, This fixes the problem:

Book16
	A	B	C	D	E	F	G	H	I
1
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
Sheet1

Formulas:

[I12]:

=(COUNTIF($C$2:$G$9,C12)>0)+(COUNTIF($C$2:$G$9,D12)>0)+(COUNTIF($C$2:$G$9,E12)>0)+(COUNTIF($C$2:$G$9,F12)>0)+(COUNTIF($C$2:$G$9,G12)>0)

 

[JohnPerk]

OK thanks a lot that seems to be working, Anything on the conditional formatting though. And one more thing if you don't mind, How would I Pick out a name from column B in referance to the highest number of matches in column I. Thanks again, If I am asking too much please tell me.