Help With Producing Results Please

Hi Everyone,

I will be Honest with you, this Request is Lotto Orientated.
I know that Anything to do with the Lotto is Usually Frowned Upon on
Newsgroups, But I am Hoping that it Being a New Year and All, that
Somebody will Kindly Help, Especially seeing that it has Nothing to do
with Prediction, it is Only to do with Information on Results.
I have the 6 Drawn Numbers in Cells E9:J1009 ( in Numerical Order for
Each Combination ).
I have the Bonus Numbers in Cells K9:K1009.
I have a List of Combinations to Check in Cells M9:R58 ( in Numerical
Order for Each Combination ).

What I would Ideally Like for EACH Combination, is the Total Times it
Matched Zero, 1, 2, 3, 4, 5, 5 + the Bonus and 6 Numbers in ALL Draws.
Obviously New Draws are Continuously be Added.

So for the First Combination for Example :-
In Cell T9 it will Show the Total Times that NO Numbers were Matched in
ALL Combinations.
In Cell U9 it will Show the Total Times that 1 Number was Matched in ALL
Combinations.
In Cell V9 it will Show the Total Times that 2 Numbers were Matched in
ALL Combinations.
In Cell W9 it will Show the Total Times that 3 Numbers were Matched in
ALL Combinations.
In Cell X9 it will Show the Total Times that 4 Numbers were Matched in
ALL Combinations.
In Cell Y9 it will Show the Total Times that 5 Numbers were Matched in
ALL Combinations.
In Cell Z9 it will Show the Total Times that 5 + the Bonus Number was
Matched in ALL Combinations.
In Cell AA9 it will Show the Total Times that 6 Numbers were Matched in
ALL Combinations.

The Second Combinations Results will be in Cells T10:AA10,
The Third Combinations Results will be in Cells T11:AA11,
The Fourth Combinations Results will be in Cells T12:AA12,
…
The Last Combinations Results will be in Cells T58:AA58.

This Makes a Total of 50 Combinations. On Occasions, the Total
Combinations to Check could be Less OR More.

I Hope Everyone has a Great New Year.
Thanks in Advance.
All the Best
Paul



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Paul,

The California lottery provides that type of information on their web site.
Maybe your lottery web site also does that?

Jim Cone
San Francisco, USA

"Paul" <[email protected]> wrote in message
news:#[email protected]...
> Hi Everyone,
> I will be Honest with you, this Request is Lotto Orientated.
> I know that Anything to do with the Lotto is Usually Frowned Upon on
> Newsgroups, But I am Hoping that it Being a New Year and All, that
> Somebody will Kindly Help, Especially seeing that it has Nothing to do
> with Prediction, it is Only to do with Information on Results.
> I have the 6 Drawn Numbers in Cells E9:J1009 ( in Numerical Order for
> Each Combination ).
> I have the Bonus Numbers in Cells K9:K1009.
> I have a List of Combinations to Check in Cells M9:R58 ( in Numerical
> Order for Each Combination ).
> What I would Ideally Like for EACH Combination, is the Total Times it
> Matched Zero, 1, 2, 3, 4, 5, 5 + the Bonus and 6 Numbers in ALL Draws.
> Obviously New Draws are Continuously be Added.
> So for the First Combination for Example :-
> In Cell T9 it will Show the Total Times that NO Numbers were Matched in
> ALL Combinations.
> In Cell U9 it will Show the Total Times that 1 Number was Matched in ALL
> Combinations.
> In Cell V9 it will Show the Total Times that 2 Numbers were Matched in
> ALL Combinations.
> In Cell W9 it will Show the Total Times that 3 Numbers were Matched in
> ALL Combinations.
> In Cell X9 it will Show the Total Times that 4 Numbers were Matched in
> ALL Combinations.
> In Cell Y9 it will Show the Total Times that 5 Numbers were Matched in
> ALL Combinations.
> In Cell Z9 it will Show the Total Times that 5 + the Bonus Number was
> Matched in ALL Combinations.
> In Cell AA9 it will Show the Total Times that 6 Numbers were Matched in
> ALL Combinations.
> The Second Combinations Results will be in Cells T10:AA10,
> The Third Combinations Results will be in Cells T11:AA11,
> The Fourth Combinations Results will be in Cells T12:AA12,
> The Last Combinations Results will be in Cells T58:AA58.
> This Makes a Total of 50 Combinations. On Occasions, the Total
> Combinations to Check could be Less OR More.
> I Hope Everyone has a Great New Year.
> Thanks in Advance.
> All the Best
> Paul

Thanks for the Reply Jim,
I live in the UK, and Our Site Unfortunately Does Not Accomodate my
Needs.
I would Ideally like a Macro to Achieve the Required Results.
Thanks Again.
All the Best
Paul



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi,

I have Managed to come up with the Following.
As I said in my Initial Post :-

I have the 6 Drawn Numbers in Cells E9:J1509 ( in Numerical Order for
Each Combination ).
I have the Bonus Numbers in Cells K9:K1509.
I have a List of Combinations to Check in Cells M9:R58 ( in Numerical
Order for Each Combination ).
In Cell B1 is the Total Number of Draws to Date.

If we take the First Combination to be Checked which is in Cells M9:R9
for Example, in Cell AD9 I have the Formula :-

=IF($M$9=0,"
",IF(SUMPRODUCT(--(ISNUMBER(MATCH($M$9:$R$9,$E9:$J9,0))))=5,IF(SUMPRODUC
T(--(ISNUMBER(MATCH($M$9:$R$9,$K9,0))))=1,"5+","5"),SUMPRODUCT(--(ISNUMB
ER(MATCH($M$9:$R$9,$E9:$J9,0))))))

which Calculates the Total Numbers Matched from Our Combination to be
Checked, Against the First Draws Combination. I then Copied this Formula
Down to Cell AD1509.

In Cell T9 I have the Formula ( to Calculate the Total Number of Zero
Matches ) :-
=IF(M9>0,SUM(B$1-SUM(U9:AA9))," ")
In Cell U9 I have the Formula ( to Calculate the Total Number of 1
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=1")
In Cell V9 I have the Formula ( to Calculate the Total Number of 2
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=2")
In Cell W9 I have the Formula ( to Calculate the Total Number of 3
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=3")
In Cell X9 I have the Formula ( to Calculate the Total Number of 4
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=4")
In Cell Y9 I have the Formula ( to Calculate the Total Number of 5
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=5")
In Cell Z9 I have the Formula ( to Calculate the Total Number of 5 + the
Bonus Matches ) :-
=COUNTIF(AD$9:AD$1509,"=5+")
In Cell AA9 I have the Formula ( to Calculate the Total Number of 6
Matches ) :-
=COUNTIF(AD$9:AD$1509,"=6")
In Cell AB9 I have the Formula ( to Calculate the Total of the Above
Number of Matches ) :-
=IF(M9>0,SUM(T9:AA9)," ")

Then the Second Combination to be Checked is in M10:R10, so the Total
Numbers Matched Formula is in AE9 and then Copied Down to AE1509, and
the Totals Matched for Each Category Formulas are in T10:AB10.

I Tried it with Checking 25 Combinations, it Created a Big File that was
Very Slow to Move Around.
Ideally a Macro would be the Best Solution, it Could Probably do this
Task in Seconds and would Only Produce Values for the Results, thereby
doing away with the Memory Hungry Formulas.
If I Wanted to Check say 50 or so Combinations, the File Size would be
Vast and Unmanageable.

Thanks in Advance.
All the Best.
Paul




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Again,

I came up with this Formula ( Formula 1 ) Using SUMPRODUCT Below :-
=IF($M$9=0,"
",IF(SUMPRODUCT(--(ISNUMBER(MATCH($M$9:$R$9,$E9:$J9,0))))=5,IF(SUMPRODUC
T(--(ISNUMBER(MATCH($M$9:$R$9,$K9,0))))=1,"5+","5"),SUMPRODUCT(--(ISNUMB
ER(MATCH($M$9:$R$9,$E9:$J9,0))))))

I also have a Working Formula ( Formula 2 ) which is Array Entered :-
=IF($C$16=0,"
",IF(SUM(COUNTIF($C$15:$H$15,T!$E9:$J9))=5,IF(SUM(COUNTIF($C$15:$H$15,T!
$K9))=1,"5+","5"),SUM(COUNTIF($C$15:$H$15,T!$E9:$J9))))

I tried Using 200 Combinations to Check Against 1,000 Draws.
I Created a File Using Formula 1 and the File Size was about 64 MB.
I then Created a File Using Formula 2, and that File Size was Also about
64 MB.
I Found that the Second File, Using Formula 2 was VERY Slow to
Calculate, Save, Load and Generally Work with in Comparison to the First
File, Using Formula 1.
Are Array Formulas Very Memory Hungry in Comparison to say Using
SUMPRODUCT Formulas.

As I Mentioned Earlier, I would Ideally like a Macro to be Able to do
this Task, NOT Only Because it would be the Best Solution, But it would
Probably Achieve the Required Results in Seconds, and would Only Produce
Values for the Results. I would like this Task to be Added to a Workbook
that is Already 30 MB in Size, and NOT having a Vast Amount of Memory
Available I cannot Afford to Include a Sheet that will EAT up Another 64
MB.

Any Help would be Greatly Appreciated.
All the Best
Paul




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Again,

I have Another Formula which Works :-

=IF($M$9=0,"
",IF(SUMPRODUCT(COUNTIF($M$9:$R$9,$E9:$J9))=5,IF(SUMPRODUCT(COUNTIF($M$9
:$R$9,$K9))=1,"5+","5"),SUMPRODUCT(COUNTIF($M$9:$R$9,$E9:$J9))))

I Again Used 200 Combinations to Check Against 1,000 Draws.
It Created a File of about 54 MB, 10 MB LESS Than Using Previous
Formulas.

If Somebody is Able to Help with a Macro, Maybe this Simplified Formula
will be Easier to Use in the Code.

Once Again, Thanks in Advance.
All the Best
Paul




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Has Anybody got Any Ideas Please?.
Any Help will be Appreciated.

All the Best
Paul



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

OK, Point Taken.
I will Class this as a Dead Request.

Thanks Anyway.
All the Best
Paul



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!