We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

"Ron" wrote:
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

One way to set it up ..

Assuming the winning numbers will be entered in A1:F1 in Sheet1,
for example: 1, 15, 25, 38, 48, 49
(The numbers within A1:F1 need not be sorted, can be in any order)

And the picked #s are listed in Sheet2, in cols B to G, row1 down
4 15 20 28 35 49
1 15 25 38 47 48
6 15 25 38 41 49
etc

(as before, the numbers within each row need not be sorted)

Put in say, I1:
=SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
Copy I1 down.

Col I will return the desired # of matched numbers
For the sample data, you'll get in I1:I3 : 2, 5, 4
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Here's a file that does what you want.

Ticket Checker.xls 84kb

http://cjoint.com/?jneKqRchxY

It has a sheet for 5 numbers and one for 6 numbers. There is one very simple
macro that clears the number grid if you click the RESET button. It will
highlight and count any matches. It uses data validation so you won't make
any mistakes when you enter your numbers.

Biff

"Ron" wrote in message
...
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

There are 7 numbers drawn so that checker won't work but thanks.

"Biff" wrote:

Here's a file that does what you want.

Ticket Checker.xls 84kb

http://cjoint.com/?jneKqRchxY

It has a sheet for 5 numbers and one for 6 numbers. There is one very simple
macro that clears the number grid if you click the RESET button. It will
highlight and count any matches. It uses data validation so you won't make
any mistakes when you enter your numbers.

Biff

"Ron" wrote in message
...
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

We're in Singapore a few times a year! We teach at Ngee Ann Poly. How wierd...
Thanks Max. I'll try this out and get back.

"Max" wrote:

"Ron" wrote:
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

One way to set it up ..

Assuming the winning numbers will be entered in A1:F1 in Sheet1,
for example: 1, 15, 25, 38, 48, 49
(The numbers within A1:F1 need not be sorted, can be in any order)

And the picked #s are listed in Sheet2, in cols B to G, row1 down
4 15 20 28 35 49
1 15 25 38 47 48
6 15 25 38 41 49
etc

(as before, the numbers within each row need not be sorted)

Put in say, I1:
=SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
Copy I1 down.

Col I will return the desired # of matched numbers
For the sample data, you'll get in I1:I3 : 2, 5, 4
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Here is an idea I used on the Texas Lottery until I decided NOT to play
anymore. Ripoff.

conditional format using formula to highlight the cell
=SUMPRODUCT(($B$5:$F$5=B9)*1)
& copy across
to count
=IF(G9=0,"",SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5)))
with conditional formatting cell value is =4

--
Don Guillett
SalesAid Software

"Ron" wrote in message
...
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

Thanks Max. It works perfectly!
And thank you to all who replied.
This is GREAT!

"Max" wrote:

"Ron" wrote:
We buy 35 tickets twice a week and always the same numbers. Can I make a
spreadsheet that will check our tickets against the winning numbers?

One way to set it up ..

Assuming the winning numbers will be entered in A1:F1 in Sheet1,
for example: 1, 15, 25, 38, 48, 49
(The numbers within A1:F1 need not be sorted, can be in any order)

And the picked #s are listed in Sheet2, in cols B to G, row1 down
4 15 20 28 35 49
1 15 25 38 47 48
6 15 25 38 41 49
etc

(as before, the numbers within each row need not be sorted)

Put in say, I1:
=SUMPRODUCT(--ISNUMBER(MATCH(B1:G1,Sheet1!$A$1:$F$1,0)))
Copy I1 down.

Col I will return the desired # of matched numbers
For the sample data, you'll get in I1:I3 : 2, 5, 4
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Glad to hear that, Ron !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Ron" wrote:
Thanks Max. It works perfectly!
And thank you to all who replied.
This is GREAT!

"Ron" wrote:
.. How weird ...

... it's ok, cyberspace knows no boundaries <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi Max,
The formula works but only for approx. 20 lottery tickets and then it
doesn't seem to check the rest. Am I doing something wrong? It's almost as if
it needs to be told to check them all???
Can anyone help?
Ron

"Max" wrote:

"Ron" wrote:
.. How weird ...

.. it's ok, cyberspace knows no boundaries <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Ron" wrote:
The formula works but only for approx. 20 lottery tickets and then it
doesn't seem to check the rest. Am I doing something wrong?
It's almost as if it needs to be told to check them all???

Try these checks, in sequence ..
1. Check calc mode is set to auto (Tools Options Calculation tab, Check
"Automatic" OK)
2. Check that cells are not inadvertently pre-formatted as text (Format
Cells). If so, re-format the cell as General or Number, then re-confirm the
formula (click inside the formula bar, press Enter) -- you need to re-confirm
the formula before it'll work. Re-formatting alone will not trigger it.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Just a further thought .. if there's the possibility of the source winning
numbers and/or picked numbers being either "text-entered" -- ie numbers
entered with a preceding apostrophe, or entered into cells pre-formatted as
text (the pre-formatting as text unknown to you, of course), then use this
slightly tweaked version instead:

=SUMPRODUCT(--ISNUMBER(MATCH(B1:G1+0,Sheet1!$A$1:$F$1+0,0)))

The "+0" to both source ranges will coerce the text numbers (if any) to real
numbers for correct results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Some were text formatted. I dhanged them all and it all works as you said.
Thanks again Max. We'll be in SQ Oct 22 thru Nov 7.
Ron in Ottawa, Canada
How's your Access?

"Max" wrote:

Just a further thought .. if there's the possibility of the source winning
numbers and/or picked numbers being either "text-entered" -- ie numbers
entered with a preceding apostrophe, or entered into cells pre-formatted as
text (the pre-formatting as text unknown to you, of course), then use this
slightly tweaked version instead:

=SUMPRODUCT(--ISNUMBER(MATCH(B1:G1+0,Sheet1!$A$1:$F$1+0,0)))

The "+0" to both source ranges will coerce the text numbers (if any) to real
numbers for correct results.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"Ron" wrote:
Some were text formatted.
I changed them all and it all works as you said.
Thanks again Max.

Glad we got that bit nailed down ..

How's your Access?
Very skimpy ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---