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(CO UNTIF($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!

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(I NDIRECT("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 <atyahoo<dotcom
----
"Paul Black" wrote in message
...
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(CO UNTIF($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!

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!

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 <atyahoo<dotcom
----
"Paul Black" wrote in message
...
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!

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 <atyahoo<dotcom
----

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!

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(CO UNTIF($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!

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!

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!