Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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 ---- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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" wrote in message ... 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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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(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 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(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 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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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" wrote in message ... 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(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 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(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 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! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
"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 <atyahoo<dotcom ---- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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" wrote in message ... 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(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 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(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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Getting Totals of Matched Numbers Please
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 wrote in message ... 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(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 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(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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are my numbers disappearing in excel yet it totals them? | Excel Worksheet Functions | |||
compare 2 columns of numbers and place the matched numbers in a 3r | Excel Discussion (Misc queries) | |||
Matching numbers in an Array and returning values for matched numb | Excel Discussion (Misc queries) | |||
turn the letters N and Y into numbers just for totals | Excel Worksheet Functions | |||
incorrect totals using rounded numbers | Excel Discussion (Misc queries) |