Help with Getting Totals of Matched Numbers Please

[Paul Black]

Hi Everyone,

I have a List of 6 Numbers in Cells E9:J1009.
I have an Extra Number in Cells K9:K1009.
I have a List of 6 Numbers that I want to Get a Total Matched for in
Cells M9:R109.

If I was to Use an Excel Formula, this would do what I want for the
First 6 Numbers to Check :-
=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:$J
9))))

I would Ideally like a Macro that takes the First Set of 6 Numbers in
Cells "M9:R9", and Uses Something Like the Formula Above to Give the
Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers in
Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using the
Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and Put
the Value in Cell "T10". Then Coninue this Process Until it Runs Out of
Numbers in the Range "E9:K1009".
Then Repeat the Above Process with the Numbers in Cells "M10:R10", and
Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers in
Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc, Until
there are NO More numbers to Check in Cells "M9:R109".
I Know it Probably Needs Something Like 2 Loops, and Using Cell Offset
to the Right for Producing the Results. So the First Set of 6 Numbers
Results will go from "T9: Whatever", the Second Set of 6 Numbers Results
will go from Cells "U9: Whatever", the Third Set of 6 Numbers Results
will go from Cells "V9: Whatever" etc.

Any Help will be Greatly Appreciated.
Thanks in Advance.
All the Best
Paul




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

[Max]

Just a quick option to play with ..

Assume you have 11 rows of numbers (in row#9 to 19) to compare/match

Fill across in T8:AD8, the 11 "row" numbers: 9,10,11 ... 19

Then put in T9:

=IF(INDIRECT("M"&T$8)=0,"",IF(SUMPRODUCT(COUNTIF(INDIRECT("M"&T$8&":R"&T$8),
$E9:$J9))=5,IF(SUMPRODUCT(COUNTIF(INDIRECT("M"&T$8&":R"&T$8),$K9))=1,"5+",5)
,SUMPRODUCT(COUNTIF(INDIRECT("M"&T$8&":R"&T$8),$E9:$J9))))

Copy T9 across to AD9,
fill down as many rows as you have data in cols M to R

Adapt to suit

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Paul Black" <[email protected]> wrote in message
news:#[email protected]...
> Hi Everyone,
>
> I have a List of 6 Numbers in Cells E9:J1009.
> I have an Extra Number in Cells K9:K1009.
> I have a List of 6 Numbers that I want to Get a Total Matched for in
> Cells M9:R109.
>
> If I was to Use an Excel Formula, this would do what I want for the
> First 6 Numbers to Check :-
> =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:$J
> 9))))
>
> I would Ideally like a Macro that takes the First Set of 6 Numbers in
> Cells "M9:R9", and Uses Something Like the Formula Above to Give the
> Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers in
> Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using the
> Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and Put
> the Value in Cell "T10". Then Coninue this Process Until it Runs Out of
> Numbers in the Range "E9:K1009".
> Then Repeat the Above Process with the Numbers in Cells "M10:R10", and
> Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers in
> Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc, Until
> there are NO More numbers to Check in Cells "M9:R109".
> I Know it Probably Needs Something Like 2 Loops, and Using Cell Offset
> to the Right for Producing the Results. So the First Set of 6 Numbers
> Results will go from "T9: Whatever", the Second Set of 6 Numbers Results
> will go from Cells "U9: Whatever", the Third Set of 6 Numbers Results
> will go from Cells "V9: Whatever" etc.
>
> Any Help will be Greatly Appreciated.
> 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 Black]

Thanks for the Reply Max,

The thing is, if I was to Use an Excel Formula to Check ALL the Numbers
I would Like to it would Create a File of About 50MB.
Using a Macro to do this so ONLY the Results Values are Output to the
Respective Cells would Not Only be Far Quicker in Respect to the
Processing Time, But would Only Create a Small File Size.

Thanks for your Help.
All the Best.
Paul



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

[Max]

Just some thoughts, Paul. To reduce file size, we could always kill all the
formulas (copy > paste special > values in-situ) in the sheet(s) once the
calcs are done. The core formula in the top left of the range, e.g. in T9
here, could be retained elsewhere and re-applied easily afresh whenever
needed. I practice this principle quite a fair bit in my work.

Do hang around awhile for better insights from others ..

Good luck !
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the Reply Max,
>
> The thing is, if I was to Use an Excel Formula to Check ALL the Numbers
> I would Like to it would Create a File of About 50MB.
> Using a Macro to do this so ONLY the Results Values are Output to the
> Respective Cells would Not Only be Far Quicker in Respect to the
> Processing Time, But would Only Create a Small File Size.
>
> Thanks for your Help.
> All the Best.
> Paul
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

[Max]

> Copy T9 across to AD9,
> fill down as many rows as you have data in cols M to R

Correction, sorry: 2nd line above should have read as:
> fill down as many rows as you have data in cols E to J

The number of cols to be copied across from the start cell T9 is
= the number of rows of data in cols M to R

Set-up is subject to the max 256 columns per sheet limit in copying across,
so this allows up to a max (256 - 9) or 247 rows of data in cols M to R
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Paul Black]

Thanks for the Reply Max,

I would have to Leave the First Row of Formulas that Produce the Results
Intact Because of the Way the Absolutes are Set. This is an Option
though, Although a Macro would be Far More Elegant and Manageable.

Thanks Again.
All the Best.
Paul



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

[Paul Black]

Hi Everyone,

I have Come Up with the Following.
Is this the Right Approach for this Problem Or have I got it Completely
Wrong.
I Don't Know How to Include the …
=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:$J
9))))
… Formula which is the Bones of the Macro Working, Or How to Accommodate
the Extra Number in Cells K9:K1009.
Perhaps Someone would be Kind Enough to Let me Know if I am on the Right
Lines Or Not Please.

Sub Matched()
Dim GroupA(1000, 6) As Integer
Dim GroupB(200, 6) As Integer
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False
Sheets("Data").Select
Range("E9:J1009").Select

i = 1

Do While ActiveCell.Value <> ""
GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
i = i + 1
ActiveCell.Offset(1, 0).Select
Loop

Sheets("Data").Select
Range("M9:R209").Select

j = 1

Do While ActiveCell.Value <> ""
GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
j = j + 1
ActiveCell.Offset(1, 0).Select
Loop

Sheets("Data").Select
Range("T9").Select

For i = 1 To 1000
For j = 1 To 200
ActiveCell.Offset(0, 1).Value = GroupA(i)
ActiveCell.Offset(1, 0).Select
Next j
Next i

Application.ScreenUpdating = True
End Sub

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 Black]

Has Anybody had a Chance to have a Look at this Yet Please.

All the Best.
Paul



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

[Paul Black]

If I am Going About this Wrong could Somebody Point me in the Right
Direction Please.

All the Best.
Paul



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

[Paul Black]

Hi Everyone,

Is there a Simpler More Compact Solution to this Please.
Is my Thinking Along the Right Lines Or Not.

Thanks in Advance.
All the Best.
Paul



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

[Tim Williams]

Paul,

When posting this kind of message you should always included your
original question - many (most) people who read this group will use a
view which hides read messages, so for me your post appears by itself
and there are no clues what you might be referring to.

Tim.


"Paul Black" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone,
>
> Is there a Simpler More Compact Solution to this Please.
> Is my Thinking Along the Right Lines Or Not.
>
> 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 Black]

Hi Tim,

You are Quite Right.
This is Actually the Second Time I have Posted this Request over the
Last Couple of Months.
I have Included Below the Original Message Along with my Feeble Macro
Attempt to Arrive at a Solution :-

Hi Everyone,

I have a List of 6 Numbers in Cells E9:J1009.
I have an Extra Number in Cells K9:K1009.
I have a List of 6 Numbers that I want to Get a Total Matched for in
Cells M9:R109.

If I was to Use an Excel Formula, this would do what I want for the
First 6 Numbers to Check :-

=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:$J
9))))

I would Ideally like a Macro that takes the First Set of 6 Numbers in
Cells "M9:R9", and Uses Something Like the Formula Above to Give the
Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers in
Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using the
Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and Put
the Value in Cell "T10". Then Continue this Process Until it Runs Out of
Numbers in the Range "E9:K1009".
Then Repeat the Above Process with the Numbers in Cells "M10:R10", and
Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers in
Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc, Until
there are NO More numbers to Check in Cells "M9:R109".
I Know it Probably Needs Something Like 2 Loops, and Using Cell Offset
to the Right for Producing the Results. So the First Set of 6 Numbers
Results will go from "T9: Whatever", the Second Set of 6 Numbers Results
will go from Cells "U9: Whatever", the Third Set of 6 Numbers Results
will go from Cells "V9: Whatever" etc.

I have Come Up with the Following.
Is this the Right Approach for this Problem Or have I got it Completely
Wrong.
I Don't Know How to Include the …

=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:$J
9))))

… Formula which is the Bones of the Macro Working, Or How to Accommodate
the Extra Number in Cells K9:K1009.
Perhaps Someone would be Kind Enough to Let me Know if I am on the Right
Lines Or Not Please.

Sub Matched()
Dim GroupA(1000, 6) As Integer
Dim GroupB(200, 6) As Integer
Dim i As Integer
Dim j As Integer

Application.ScreenUpdating = False
Sheets("Data").Select
Range("E9:J1009").Select

i = 1

Do While ActiveCell.Value <> ""
GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
i = i + 1
ActiveCell.Offset(1, 0).Select
Loop

Sheets("Data").Select
Range("M9:R209").Select

j = 1

Do While ActiveCell.Value <> ""
GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
j = j + 1
ActiveCell.Offset(1, 0).Select
Loop

Sheets("Data").Select
Range("T9").Select

For i = 1 To 1000
For j = 1 To 200
ActiveCell.Offset(0, 1).Value = GroupA(i)
ActiveCell.Offset(1, 0).Select
Next j
Next i

Application.ScreenUpdating = True
End Sub

Any Help will be Greatly Appreciated.
Thanks in Advance.
All the Best
Paul




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

[Tim Williams]

I can see why you haven't had much in the way of suggestions yet:
after 5 mins looking over your explanation I'm still not sure exactly
what you're doing.
What is the significance of the "extra number" in K ?

If you'd like to send me an example via e-mail (fix my address...) I
can take a look: always easier with an example.


Tim.


"Paul Black" <[email protected]> wrote in message
news:%[email protected]...
> Hi Tim,
>
> You are Quite Right.
> This is Actually the Second Time I have Posted this Request over the
> Last Couple of Months.
> I have Included Below the Original Message Along with my Feeble
> Macro
> Attempt to Arrive at a Solution :-
>
> Hi Everyone,
>
> I have a List of 6 Numbers in Cells E9:J1009.
> I have an Extra Number in Cells K9:K1009.
> I have a List of 6 Numbers that I want to Get a Total Matched for in
> Cells M9:R109.
>
> If I was to Use an Excel Formula, this would do what I want for the
> First 6 Numbers to Check :-
>
> =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:$J
> 9))))
>
> I would Ideally like a Macro that takes the First Set of 6 Numbers
> in
> Cells "M9:R9", and Uses Something Like the Formula Above to Give the
> Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers
> in
> Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using
> the
> Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and
> Put
> the Value in Cell "T10". Then Continue this Process Until it Runs
> Out of
> Numbers in the Range "E9:K1009".
> Then Repeat the Above Process with the Numbers in Cells "M10:R10",
> and
> Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers
> in
> Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc,
> Until
> there are NO More numbers to Check in Cells "M9:R109".
> I Know it Probably Needs Something Like 2 Loops, and Using Cell
> Offset
> to the Right for Producing the Results. So the First Set of 6
> Numbers
> Results will go from "T9: Whatever", the Second Set of 6 Numbers
> Results
> will go from Cells "U9: Whatever", the Third Set of 6 Numbers
> Results
> will go from Cells "V9: Whatever" etc.
>
> I have Come Up with the Following.
> Is this the Right Approach for this Problem Or have I got it
> Completely
> Wrong.
> I Don't Know How to Include the .
>
> =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:$J
> 9))))
>
> . Formula which is the Bones of the Macro Working, Or How to
> Accommodate
> the Extra Number in Cells K9:K1009.
> Perhaps Someone would be Kind Enough to Let me Know if I am on the
> Right
> Lines Or Not Please.
>
> Sub Matched()
> Dim GroupA(1000, 6) As Integer
> Dim GroupB(200, 6) As Integer
> Dim i As Integer
> Dim j As Integer
>
> Application.ScreenUpdating = False
> Sheets("Data").Select
> Range("E9:J1009").Select
>
> i = 1
>
> Do While ActiveCell.Value <> ""
> GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
> GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
> GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
> GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
> GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
> GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
> i = i + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("M9:R209").Select
>
> j = 1
>
> Do While ActiveCell.Value <> ""
> GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
> GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
> GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
> GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
> GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
> GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
> j = j + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("T9").Select
>
> For i = 1 To 1000
> For j = 1 To 200
> ActiveCell.Offset(0, 1).Value = GroupA(i)
> ActiveCell.Offset(1, 0).Select
> Next j
> Next i
>
> Application.ScreenUpdating = True
> End Sub
>
> Any Help will be Greatly Appreciated.
> Thanks in Advance.
> All the Best
> Paul
>
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

[Max]

"Tim Williams" <saxifrax@pacbell*dot*net> wrote
....
> What is the significance of the "extra number" in K ?

Believe this is the extra number that's drawn in lotto games
which enables one to match say, any 5 numbers out of an original 6 numbers
drawn (e.g. for a 6/49 game) *plus* this extra number to get a share of the
2nd prize pot ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----

[Paul Black]

Hi Tim,

Max is Exactly Right ( Thanks Max ), it is for the Bonus Ball in a
Lotto, I should have Mentioned that. The Formula Works, But for ALL
Cases Creates such a Big Excel File it is VERY Slow and Unmanageable. My
thoughts were that Using a Macro to Achieve this would be a Lot Faster
Because it would ONLY put the Values in the Cells as Opposed to having
Formulas in the Cells.

Thanks Very Much in Advance.
All the Best.
Paul

"Paul Black" <[email protected]> wrote in message
news:%[email protected]...
> Hi Tim,
>
> You are Quite Right.
> This is Actually the Second Time I have Posted this > Request
over the Last Couple of Months.
> I have Included Below the Original Message Along with my > Feeble
Macro Attempt to Arrive at a Solution :-
>
> Hi Everyone,
>
> I have a List of 6 Numbers in Cells E9:J1009.
> I have an Extra Number in Cells K9:K1009.
> I have a List of 6 Numbers that I want to Get a Total > Matched for
in Cells M9:R109.
>
> If I was to Use an Excel Formula, this would do what I > want for the
First 6 Numbers to Check :-
>
> =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:$J
> 9))))
>
> I would Ideally like a Macro that takes the First Set of > 6 Numbers
in
> Cells "M9:R9", and Uses Something Like the Formula Above > to Give the
Value of the Total Numbers Matched in > Cells "M9:R9" with the
Numbers in Cells "E9:K9" and Put > the Value in Cell "T9". Then Carry on
Using the Same 6 > Numbers in Cells "M9:R9" But Match with
> Cells "E10:K10" and Put the Value in Cell "T10". Then > Continue
this Process Until it Runs Out of Numbers in > the Range "E9:K1009".
> Then Repeat the Above Process with the Numbers in > Cells
"M10:R10", and Put the Value in > Cell "U9", "U10",
"U11" etc, then Use the Numbers in > Cells "M11:R11", and Put the
Value in > Cell "V9", "V10", "V11" etc, Until there
are NO More > numbers to Check in Cells "M9:R109".
> I Know it Probably Needs Something Like 2 Loops, and > Using Cell
Offset to the Right for Producing the > Results. So the First Set
of 6 Numbers Results will go > from "T9: Whatever", the Second Set of 6
Numbers
> Results will go from Cells "U9: Whatever", the Third Set > of 6
Numbers Results will go from Cells "V9: Whatever" > etc.
>
> I have Come Up with the Following.
> Is this the Right Approach for this Problem Or have I > got it
Completely Wrong.
> I Don't Know How to Include the ...
>
> =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:$J
> 9))))
>
> ... Formula which is the Bones of the Macro Working, Or > How to
Accommodate the Extra Number in Cells K9:K1009.
> Perhaps Someone would be Kind Enough to Let me Know if I > am on the
Right Lines Or Not Please.
>
> Sub Matched()
> Dim GroupA(1000, 6) As Integer
> Dim GroupB(200, 6) As Integer
> Dim i As Integer
> Dim j As Integer
>
> Application.ScreenUpdating = False
> Sheets("Data").Select
> Range("E9:J1009").Select
>
> i = 1
>
> Do While ActiveCell.Value <> ""
> GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
> GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
> GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
> GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
> GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
> GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
> i = i + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("M9:R209").Select
>
> j = 1
>
> Do While ActiveCell.Value <> ""
> GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
> GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
> GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
> GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
> GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
> GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
> j = j + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("T9").Select
>
> For i = 1 To 1000
> For j = 1 To 200
> ActiveCell.Offset(0, 1).Value = GroupA(i)
> ActiveCell.Offset(1, 0).Select
> Next j
> Next i
>
> Application.ScreenUpdating = True
> End Sub



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

[Paul Black]

Sorry Guys,

I Don't Know why the Above Post Got Screwed Up, I have had Another Go
Below.

Hi Tim,

Max is Exactly Right ( Thanks Max ), it is for the Bonus Ball in a
Lotto, I should have Mentioned that. The Formula Works, But for ALL
Cases Creates such a Big Excel File it is VERY Slow and Unmanageable. My
thoughts were that Using a Macro to Achieve this would be a Lot Faster
Because it would ONLY put the Values in the Cells as Opposed to having
Formulas in the Cells.

Thanks Very Much in Advance.
All the Best.
Paul

Paul Black <[email protected]> wrote in message
news:%[email protected]...
> Hi Tim,
>
> You are Quite Right.
> This is Actually the Second Time I have Posted this Request over the
> Last Couple of Months.
> I have Included Below the Original Message Along with my Feeble
> Macro
> Attempt to Arrive at a Solution :-
>
> Hi Everyone,
>
> I have a List of 6 Numbers in Cells E9:J1009.
> I have an Extra Number in Cells K9:K1009.
> I have a List of 6 Numbers that I want to Get a Total Matched for in
> Cells M9:R109.
>
> If I was to Use an Excel Formula, this would do what I want for the
> First 6 Numbers to Check :-
>
>
=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:$J
> 9))))
>
> I would Ideally like a Macro that takes the First Set of 6 Numbers
> in
> Cells "M9:R9", and Uses Something Like the Formula Above to Give the
> Value of the Total Numbers Matched in Cells "M9:R9" with the Numbers
> in
> Cells "E9:K9" and Put the Value in Cell "T9". Then Carry on Using
> the
> Same 6 Numbers in Cells "M9:R9" But Match with Cells "E10:K10" and
> Put
> the Value in Cell "T10". Then Continue this Process Until it Runs
> Out of
> Numbers in the Range "E9:K1009".
> Then Repeat the Above Process with the Numbers in Cells "M10:R10",
> and
> Put the Value in Cell "U9", "U10", "U11" etc, then Use the Numbers
> in
> Cells "M11:R11", and Put the Value in Cell "V9", "V10", "V11" etc,
> Until
> there are NO More numbers to Check in Cells "M9:R109".
> I Know it Probably Needs Something Like 2 Loops, and Using Cell
> Offset
> to the Right for Producing the Results. So the First Set of 6
> Numbers
> Results will go from "T9: Whatever", the Second Set of 6 Numbers
> Results
> will go from Cells "U9: Whatever", the Third Set of 6 Numbers
> Results
> will go from Cells "V9: Whatever" etc.
>
> I have Come Up with the Following.
> Is this the Right Approach for this Problem Or have I got it
> Completely
> Wrong.
> I Don't Know How to Include the .
>
>
=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:$J
> 9))))
>
> . Formula which is the Bones of the Macro Working, Or How to
> Accommodate
> the Extra Number in Cells K9:K1009.
> Perhaps Someone would be Kind Enough to Let me Know if I am on the
> Right
> Lines Or Not Please.
>
> Sub Matched()
> Dim GroupA(1000, 6) As Integer
> Dim GroupB(200, 6) As Integer
> Dim i As Integer
> Dim j As Integer
>
> Application.ScreenUpdating = False
> Sheets("Data").Select
> Range("E9:J1009").Select
>
> i = 1
>
> Do While ActiveCell.Value <> ""
> GroupA(i, 1) = ActiveCell.Offset(0, 0).Value
> GroupA(i, 2) = ActiveCell.Offset(0, 1).Value
> GroupA(i, 3) = ActiveCell.Offset(0, 2).Value
> GroupA(i, 4) = ActiveCell.Offset(0, 3).Value
> GroupA(i, 5) = ActiveCell.Offset(0, 4).Value
> GroupA(i, 6) = ActiveCell.Offset(0, 5).Value
> i = i + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("M9:R209").Select
>
> j = 1
>
> Do While ActiveCell.Value <> ""
> GroupB(j, 1) = ActjveCell.Offset(0, 0).Value
> GroupB(j, 2) = ActjveCell.Offset(0, 1).Value
> GroupB(j, 3) = ActjveCell.Offset(0, 2).Value
> GroupB(j, 4) = ActjveCell.Offset(0, 3).Value
> GroupB(j, 5) = ActjveCell.Offset(0, 4).Value
> GroupB(j, 6) = ActjveCell.Offset(0, 5).Value
> j = j + 1
> ActiveCell.Offset(1, 0).Select
> Loop
>
> Sheets("Data").Select
> Range("T9").Select
>
> For i = 1 To 1000
> For j = 1 To 200
> ActiveCell.Offset(0, 1).Value = GroupA(i)
> ActiveCell.Offset(1, 0).Select
> Next j
> Next i
>
> Application.ScreenUpdating = True
> End Sub


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