Help with Getting Totals of Matched Numbers Please

P

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
 
M

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
 
P

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
 
M

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 !
 
M

Max

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

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

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
 
P

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
 
P

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
 
P

Paul Black

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

All the Best.
Paul
 
P

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
 
T

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.
 
P

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
 
T

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.
 
M

Max

Tim Williams said:
What is the significance of the "extra number" in K ?
Click to expand...

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 ..
 
P

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 said:
Hi Tim,

You are Quite Right.
This is Actually the Second Time I have Posted this > Request
Click to expand...
over the Last Couple of Months.
I have Included Below the Original Message Along with my > Feeble
Click to expand...
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
Click to expand...
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
Click to expand...
this Process Until it Runs Out of Numbers in > the Range "E9:K1009".
Then Repeat the Above Process with the Numbers in > Cells
Click to expand...
"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
Click to expand...
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
Click to expand...
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
Click to expand...
Accommodate the Extra Number in Cells K9:K1009.
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help With Producing Results Please 7
Selection_Change? 2
Select and sum all numbered cells in Column A 2
SUM cells with blank value 10
Use only cells with data in 7
Help with Totals Please 13
Sumproduct Help Please 8
Code to insert a coma 26

Top