Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totals Please
Hi Everyone,
I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much 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 Totals Please
Paul
You might like to explain the result a little clearer. I can't make any sense of the results you have. Steve "Paul Black" wrote in message ... Hi Everyone, I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much 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 Totals Please
Thanks for the Reply Steve,
If I Use the First Set of Numbers in the Cells J3:O10 which are 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the First Set of Numbers in Cells C3:H10 which are 01 02 03 04 05 06, the Answer is 3, and this Figure will go in Cell Q3. Then Using the Same Numbers 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the Second Set of Numbers in Cells C3:H10 which are 02 03 04 05 06 07, the Answer is 4, and this Figure will go in Cell Q4. I have Simplified the Table to Show the Total Matches for the First Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 Ans = 3 in Cell Q3 02 03 04 05 06 07 Ans = 4 in Cell Q4 03 04 05 06 07 08 Ans = 5 in Cell Q5 04 05 06 07 08 09 Ans = 6 in Cell Q6 05 06 07 08 09 10 Ans = 5 in Cell Q7 06 07 08 09 10 11 Ans = 4 in Cell Q8 07 08 09 10 11 12 Ans = 3 in Cell Q9 08 09 10 11 12 13 Ans = 2 in Cell Q10 09 10 11 12 13 14 Ans = 1 in Cell Q11 10 11 12 13 14 15 Ans = 0 in Cell Q12 Then the Total Matches for the Second Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 Ans = 2 in Cell R3 02 03 04 05 06 07 05 06 07 08 09 10 Ans = 3 in Cell R4 03 04 05 06 07 08 Ans = 4 in Cell R5 04 05 06 07 08 09 Ans = 4 in Cell R6 05 06 07 08 09 10 Ans = 6 in Cell R7 06 07 08 09 10 11 Ans = 5 in Cell R8 07 08 09 10 11 12 Ans = 4 in Cell R9 08 09 10 11 12 13 Ans = 3 in Cell R10 09 10 11 12 13 14 Ans = 2 in Cell R11 10 11 12 13 14 15 Ans = 1 in Cell R12 Then the Third Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 and so on. I think this will Probably Need SumProduct Or Match Or Something in the Macro to Achieve this. Going through ALL the Sets of Numbers in Cells J3:O10 and Cells C3:H10 for the Total Matches for Each Set, the Final Results will be in Cells Z3:Z12. I Hope I have Made this Easier to Understand. Thanks Again. All the Best. Paul From: Steve Paul You might like to explain the result a little clearer. I can't make any sense of the results you have. Steve "Paul Black" wrote in message ... Hi Everyone, I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much in Advance. 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 Totals Please
Paul
I started with a Sub but decided and array function should be able to do this easily. Try entering the following at Q3 using Ctrl/Shift/Enter =COUNT(MATCH(J$3:O$3,C3:H3,0)*1) Use the fill handle to drag it down. I need to nut out the rest of the formula to return the right references when dragged across but have to cease now until tomorrow. HTH Steve "Paul Black" wrote in message ... Thanks for the Reply Steve, If I Use the First Set of Numbers in the Cells J3:O10 which are 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the First Set of Numbers in Cells C3:H10 which are 01 02 03 04 05 06, the Answer is 3, and this Figure will go in Cell Q3. Then Using the Same Numbers 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the Second Set of Numbers in Cells C3:H10 which are 02 03 04 05 06 07, the Answer is 4, and this Figure will go in Cell Q4. I have Simplified the Table to Show the Total Matches for the First Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 Ans = 3 in Cell Q3 02 03 04 05 06 07 Ans = 4 in Cell Q4 03 04 05 06 07 08 Ans = 5 in Cell Q5 04 05 06 07 08 09 Ans = 6 in Cell Q6 05 06 07 08 09 10 Ans = 5 in Cell Q7 06 07 08 09 10 11 Ans = 4 in Cell Q8 07 08 09 10 11 12 Ans = 3 in Cell Q9 08 09 10 11 12 13 Ans = 2 in Cell Q10 09 10 11 12 13 14 Ans = 1 in Cell Q11 10 11 12 13 14 15 Ans = 0 in Cell Q12 Then the Total Matches for the Second Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 Ans = 2 in Cell R3 02 03 04 05 06 07 05 06 07 08 09 10 Ans = 3 in Cell R4 03 04 05 06 07 08 Ans = 4 in Cell R5 04 05 06 07 08 09 Ans = 4 in Cell R6 05 06 07 08 09 10 Ans = 6 in Cell R7 06 07 08 09 10 11 Ans = 5 in Cell R8 07 08 09 10 11 12 Ans = 4 in Cell R9 08 09 10 11 12 13 Ans = 3 in Cell R10 09 10 11 12 13 14 Ans = 2 in Cell R11 10 11 12 13 14 15 Ans = 1 in Cell R12 Then the Third Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 and so on. I think this will Probably Need SumProduct Or Match Or Something in the Macro to Achieve this. Going through ALL the Sets of Numbers in Cells J3:O10 and Cells C3:H10 for the Total Matches for Each Set, the Final Results will be in Cells Z3:Z12. I Hope I have Made this Easier to Understand. Thanks Again. All the Best. Paul From: Steve Paul You might like to explain the result a little clearer. I can't make any sense of the results you have. Steve "Paul Black" wrote in message ... Hi Everyone, I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much in Advance. 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 Totals Please
Thanks for the Reply Steve,
I have come up with the Following ( Thanks to your Formula ) which Obviously ONLY Works for the First Set of Numbers ( 04 05 06 07 08 09 ) in Cell J3:O3 Matched with the First Set of Numbers ( 01 02 03 04 05 06 ) in Cells C3:H3. What I am Unable to Grasp is how this can be put into a Loop so that it goes through ALL the Cells in C3:H10 and then Moves to Cells J4:O4 and do the Same Loop and Continue Until ALL the Cells have Been Looped through. Sub Test() With Application Worksheets("Data").Range("Q3") = Evaluate("=COUNT(MATCH(J3:O3,C3:H3,0)*1)") End With End Sub Thanks in Advance. All the Best. Paul Paul I started with a Sub but decided and array function should be able to do this easily. Try entering the following at Q3 using Ctrl/Shift/Enter =COUNT(MATCH(J$3:O$3,C3:H3,0)*1) Use the fill handle to drag it down. I need to nut out the rest of the formula to return the right references when dragged across but have to cease now until tomorrow. HTH Steve "Paul Black" wrote in message ... Thanks for the Reply Steve, If I Use the First Set of Numbers in the Cells J3:O10 which are 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the First Set of Numbers in Cells C3:H10 which are 01 02 03 04 05 06, the Answer is 3, and this Figure will go in Cell Q3. Then Using the Same Numbers 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the Second Set of Numbers in Cells C3:H10 which are 02 03 04 05 06 07, the Answer is 4, and this Figure will go in Cell Q4. I have Simplified the Table to Show the Total Matches for the First Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 Ans = 3 in Cell Q3 02 03 04 05 06 07 Ans = 4 in Cell Q4 03 04 05 06 07 08 Ans = 5 in Cell Q5 04 05 06 07 08 09 Ans = 6 in Cell Q6 05 06 07 08 09 10 Ans = 5 in Cell Q7 06 07 08 09 10 11 Ans = 4 in Cell Q8 07 08 09 10 11 12 Ans = 3 in Cell Q9 08 09 10 11 12 13 Ans = 2 in Cell Q10 09 10 11 12 13 14 Ans = 1 in Cell Q11 10 11 12 13 14 15 Ans = 0 in Cell Q12 Then the Total Matches for the Second Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 Ans = 2 in Cell R3 02 03 04 05 06 07 05 06 07 08 09 10 Ans = 3 in Cell R4 03 04 05 06 07 08 Ans = 4 in Cell R5 04 05 06 07 08 09 Ans = 4 in Cell R6 05 06 07 08 09 10 Ans = 6 in Cell R7 06 07 08 09 10 11 Ans = 5 in Cell R8 07 08 09 10 11 12 Ans = 4 in Cell R9 08 09 10 11 12 13 Ans = 3 in Cell R10 09 10 11 12 13 14 Ans = 2 in Cell R11 10 11 12 13 14 15 Ans = 1 in Cell R12 Then the Third Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 and so on. I think this will Probably Need SumProduct Or Match Or Something in the Macro to Achieve this. Going through ALL the Sets of Numbers in Cells J3:O10 and Cells C3:H10 for the Total Matches for Each Set, the Final Results will be in Cells Z3:Z12. I Hope I have Made this Easier to Understand. Thanks Again. All the Best. Paul From: Steve Paul You might like to explain the result a little clearer. I can't make any sense of the results you have. Steve "Paul Black" wrote in message ... Hi Everyone, I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Totals Please
Paul
Enter at Q2 ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Array enter at Q3 =COUNT(MATCH(INDIRECT(Q$2),$C3:$H3,0)*1) Use the fill handle to drag the Q2 formula across the sheet. Use the fill handle to drage the Q3 formula down and across the sheet. When I tried entering the Q2 portion of the formula in the Q3 formula in place of the Q$2 reference, it did not work for me (the reason I gave up yesterday). You may find the following works for you as an array formula in Q3. If so, use it, omitting the Q2 formula. =COUNT(MATCH(INDIRECT("$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14),$C3:$H3,0)*1) If you want an explanation of any of the above, just ask. HTH Steve "Paul Black" wrote in message ... Thanks for the Reply Steve, I have come up with the Following ( Thanks to your Formula ) which Obviously ONLY Works for the First Set of Numbers ( 04 05 06 07 08 09 ) in Cell J3:O3 Matched with the First Set of Numbers ( 01 02 03 04 05 06 ) in Cells C3:H3. What I am Unable to Grasp is how this can be put into a Loop so that it goes through ALL the Cells in C3:H10 and then Moves to Cells J4:O4 and do the Same Loop and Continue Until ALL the Cells have Been Looped through. Sub Test() With Application Worksheets("Data").Range("Q3") = Evaluate("=COUNT(MATCH(J3:O3,C3:H3,0)*1)") End With End Sub Thanks in Advance. All the Best. Paul Paul I started with a Sub but decided and array function should be able to do this easily. Try entering the following at Q3 using Ctrl/Shift/Enter =COUNT(MATCH(J$3:O$3,C3:H3,0)*1) Use the fill handle to drag it down. I need to nut out the rest of the formula to return the right references when dragged across but have to cease now until tomorrow. HTH Steve "Paul Black" wrote in message ... Thanks for the Reply Steve, If I Use the First Set of Numbers in the Cells J3:O10 which are 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the First Set of Numbers in Cells C3:H10 which are 01 02 03 04 05 06, the Answer is 3, and this Figure will go in Cell Q3. Then Using the Same Numbers 04 05 06 07 08 09, I want to get the Total of those Numbers Matched with the Second Set of Numbers in Cells C3:H10 which are 02 03 04 05 06 07, the Answer is 4, and this Figure will go in Cell Q4. I have Simplified the Table to Show the Total Matches for the First Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 Ans = 3 in Cell Q3 02 03 04 05 06 07 Ans = 4 in Cell Q4 03 04 05 06 07 08 Ans = 5 in Cell Q5 04 05 06 07 08 09 Ans = 6 in Cell Q6 05 06 07 08 09 10 Ans = 5 in Cell Q7 06 07 08 09 10 11 Ans = 4 in Cell Q8 07 08 09 10 11 12 Ans = 3 in Cell Q9 08 09 10 11 12 13 Ans = 2 in Cell Q10 09 10 11 12 13 14 Ans = 1 in Cell Q11 10 11 12 13 14 15 Ans = 0 in Cell Q12 Then the Total Matches for the Second Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10. Set 1 Set 2 Matched Cells C3:H10 Cells J3:O10 Results -------------------------------------------------------- 01 02 03 04 05 06 Ans = 2 in Cell R3 02 03 04 05 06 07 05 06 07 08 09 10 Ans = 3 in Cell R4 03 04 05 06 07 08 Ans = 4 in Cell R5 04 05 06 07 08 09 Ans = 4 in Cell R6 05 06 07 08 09 10 Ans = 6 in Cell R7 06 07 08 09 10 11 Ans = 5 in Cell R8 07 08 09 10 11 12 Ans = 4 in Cell R9 08 09 10 11 12 13 Ans = 3 in Cell R10 09 10 11 12 13 14 Ans = 2 in Cell R11 10 11 12 13 14 15 Ans = 1 in Cell R12 Then the Third Set of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 and so on. I think this will Probably Need SumProduct Or Match Or Something in the Macro to Achieve this. Going through ALL the Sets of Numbers in Cells J3:O10 and Cells C3:H10 for the Total Matches for Each Set, the Final Results will be in Cells Z3:Z12. I Hope I have Made this Easier to Understand. Thanks Again. All the Best. Paul From: Steve Paul You might like to explain the result a little clearer. I can't make any sense of the results you have. Steve "Paul Black" wrote in message ... Hi Everyone, I would like A Macro to Calculate the Following Please. I have Used 10 Sets of Numbers for the General Idea of what I would like to Achieve. I can then Adjust the Macro for Larger Sets of Numbers if Required. I have 10 Sets of Numbers in the Range C3:H10. I have 10 Sets of Numbers in the Range J3:O10. It is Basically Getting the Total Numbers Matched for Each Set of Numbers in Cells J3:O10 with Each Set of Numbers in Cells C3:H10. In More Detail :- I would like the Total Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C3:H3, then the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C4:H4 etc, the Last One Being for the Total of Numbers Matched for the Set of Numbers in Cells J3:O3 with the Set of Numbers in Cells C10:H10. The Output of Numbers Matched would be in Cells Q3:Q12. Then I would like Exactly the Same thing But Using the Set of Numbers in Cells J4:O4 Matched with ALL the Sets of Numbers in Cells C3:H10. The Output of Numbers Matched would be in Cells R3:R12. And so on Until ALL the Sets of Numbers in Cells J3:O10 have Been Matched with ALL the Sets of Numbers in Cells C3:H10. The Output for the Last Set of Numbers Matched would be in Cells Z3:Z12. For Example, Matching the First Three Sets of Numbers in Cells J3:O10 with ALL the Sets of Numbers in Cells C3:H10 would be :- Set 1 Set 2 Cells C3:H10 Cells J3:O10 Results ------------------------------------------------- 01 02 03 04 05 06 04 05 06 07 08 09 3 2 0 02 03 04 05 06 07 05 06 07 08 09 10 4 3 1 03 04 05 06 07 08 07 08 09 10 11 12 5 4 2 04 05 06 07 08 09 02 03 04 05 06 07 6 4 3 05 06 07 08 09 10 01 02 03 04 11 12 5 6 4 06 07 08 09 10 11 10 11 12 13 14 15 4 5 5 07 08 09 10 11 12 01 02 03 04 05 06 3 4 6 08 09 10 11 12 13 05 06 07 08 09 10 2 3 5 09 10 11 12 13 14 05 06 07 08 09 10 1 2 4 10 11 12 13 14 15 01 03 05 07 09 11 0 1 3 ------------------------------------------------- Thanks Very Much in Advance. 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 Totals Please
Hi Steve,
Thanks for the Formulas. The ALL in One Formula did NOT Work, But the Two Seperate Formulas DID, although I Don't Really Understand the Why. Perhaps you could give be a Brief Explanation Please. I Tested it out Using 1,000 Sets of Numbers in Cells C3:H1003 and 200 Sets of Numbers in Cells J3:O203, it Created a VERY Big Excel File which Runs Very Slowly. I think if this could be Achieved in VBA it will make it Far More Manageable and Extremely Quick. Thanks Very Much for your Time. Cheers. All the Best. Paul Help with Totals Please From: Steve Paul Enter at Q2 ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Array enter at Q3 =COUNT(MATCH(INDIRECT(Q$2),$C3:$H3,0)*1) Use the fill handle to drag the Q2 formula across the sheet. Use the fill handle to drage the Q3 formula down and across the sheet. When I tried entering the Q2 portion of the formula in the Q3 formula in place of the Q$2 reference, it did not work for me (the reason I gave up yesterday). You may find the following works for you as an array formula in Q3. If so, use it, omitting the Q2 formula. =COUNT(MATCH(INDIRECT("$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14),$C3:$H3,0)*1) If you want an explanation of any of the above, just ask. HTH Steve *** 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 Totals Please
Paul
Because of the amount of cross-checking (six values against six values in each row of your two arrays), I doubt a macro would be much faster. But, I'm often wrong. It may help your performance problem if you turn off automatic calculation when your workbook is opened (turn it back on again when the workbook closes). When you make data changes and are ready, run a manual calculation. Turn off screen updating when it runs. You can automate all this with macros. Just turn on your macro recorder, record each separate action. copy the appropriate parts to "Workbook_Open" and "Workbook_BeforeClose" events. A macro button, keyboard shortcut or strategic addition to another macro can take care of the calculations when you require it happen. The reason the formula worked in two separate halves and not as a whole is beyond my comprehension. My guess is that it is a feature of an array formula work. Before attempting to read my explanation below, you might like to have a look at the Help reference "About array formulas and how to enter them". Also, a seach of Google using ' Excel "Array formula" ' will provide a wealth of information including ready-made solutions to common problems. Interpretting my formula. First formula =COUNT(MATCH($J$3:$O$3,$C3:$H3,0)*1) The problem as I interpreted it was to find the number of values in a row in one array that matched the values in the row of another array. Countif() with handle finding a single number in an array but it won't handle all matches in two arrays. This is where an array function is useful. Some of the methods I found in a search of the web were able to solve the problem but, only if the values were in equivalent positions within the array (eg if '1' was the 2nd number in array A, it needed to be the second number in array B). Match() solved the problem in that looked for an equal value in any part of the array. However, Match() on its own returned incorrect result. If you look up Match in Help, youl find that it returns a number relative to the position of the located value in an array (eg 1,2,3,4). Count() fixed that by returning only the number of values returned by Match(). Second part of the formula ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 The problem with the first part of the formula is that it could not be set so that array of numbers from columns J to O did not change as the row changed but did change on column change. The Column() function can be used to work around this. Column() returns a column realting to the cell location (copy '=Column' into a spreadsheet and use the fill handle to see what it does). To use this to relate to a row number is just mathematic Q is the 17th letter of the alphabet. Take away '14' and that leaves row three. Drag it to column R and the return is '4'. Wrap that into an equation using some strings values for the rest of the equation and the result is a reference that changes by a row when as the column changes. Excel does not recognise the above as a valid range reference unless it is enclosed in the Indirect() function. I hope my explanation was understandable. BTW In analysing the equation again, I just discovered that the '*1' at the end of the equation appears superfluous. Remove it. It may help performance a little. Steve "Paul Black" wrote in message ... Hi Steve, Thanks for the Formulas. The ALL in One Formula did NOT Work, But the Two Seperate Formulas DID, although I Don't Really Understand the Why. Perhaps you could give be a Brief Explanation Please. I Tested it out Using 1,000 Sets of Numbers in Cells C3:H1003 and 200 Sets of Numbers in Cells J3:O203, it Created a VERY Big Excel File which Runs Very Slowly. I think if this could be Achieved in VBA it will make it Far More Manageable and Extremely Quick. Thanks Very Much for your Time. Cheers. All the Best. Paul Help with Totals Please From: Steve Paul Enter at Q2 ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Array enter at Q3 =COUNT(MATCH(INDIRECT(Q$2),$C3:$H3,0)*1) Use the fill handle to drag the Q2 formula across the sheet. Use the fill handle to drage the Q3 formula down and across the sheet. When I tried entering the Q2 portion of the formula in the Q3 formula in place of the Q$2 reference, it did not work for me (the reason I gave up yesterday). You may find the following works for you as an array formula in Q3. If so, use it, omitting the Q2 formula. =COUNT(MATCH(INDIRECT("$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14),$C3:$H3,0)*1) If you want an explanation of any of the above, just ask. HTH Steve *** 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 Totals Please
Hi Steve,
Excellent, Thank You Very Much. I have Fiddled Around with my File and have got it All Working Great. As Far as Creating a Big Excel File if there were Many Sets of Numbers, what I will do is to Just have the Formula Input in Cell Q3 and Save the File. Then when I want to Test Against "X" Sets of Numbers I can Copy the Formula Across AND Down as Required, and then Close Without Saving. One Last Question Please. If I Wanted the Total for Each Number of Matches for EACH Set, How would I Create the Formula. Let me Give you an Example. Lets Say we have Matched 10 Sets of Numbers, and the Results are in Columns Q to Z ( Q3:Z12). For the First Set of Numbers we would have :- In Cell AB3, the Total of 0 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=0") In Cell AC3, the Total of 1 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=1") In Cell AD3, the Total of 2 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=2") In Cell AE3, the Total of 3 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=3") In Cell AF3, the Total of 4 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=4") In Cell AG3, the Total of 5 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=5") In Cell AH3, the Total of 6 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=6") Then if we Drag ( Copy ) these Formulas from AB3:AH3 Down we come up with the Same Problem as Before in Regard to Rows and Columns. We would want the Second Set of Numbers Totals to be :- In Cell AB4, the Total of 0 Matches, the Formula would be =CountIf($R$3:$R$12,"=0") In Cell AC4, the Total of 1 Matches, the Formula would be =CountIf($R$3:$R$12,"=1") In Cell AD4, the Total of 2 Matches, the Formula would be =CountIf($R$3:$R$12,"=2") In Cell AE4, the Total of 3 Matches, the Formula would be =CountIf($R$3:$R$12,"=3") In Cell AF4, the Total of 4 Matches, the Formula would be =CountIf($R$3:$R$12,"=4") In Cell AG4, the Total of 5 Matches, the Formula would be =CountIf($R$3:$R$12,"=5") In Cell AH4, the Total of 6 Matches, the Formula would be =CountIf($R$3:$R$12,"=6") I have Tried to Use the Theory of your Formula to Account for this But Without ANY Success. Where as the First One was Rows to Columns, this is Columns to Rows. Achieving this would Finish Off the File Nicely. Thanks in Advance. All the Best. Paul Help with Totals Please From: Steve Paul Because of the amount of cross-checking (six values against six values in each row of your two arrays), I doubt a macro would be much faster. But, I'm often wrong. It may help your performance problem if you turn off automatic calculation when your workbook is opened (turn it back on again when the workbook closes). When you make data changes and are ready, run a manual calculation. Turn off screen updating when it runs. You can automate all this with macros. Just turn on your macro recorder, record each separate action. copy the appropriate parts to "Workbook_Open" and "Workbook_BeforeClose" events. A macro button, keyboard shortcut or strategic addition to another macro can take care of the calculations when you require it happen. The reason the formula worked in two separate halves and not as a whole is beyond my comprehension. My guess is that it is a feature of an array formula work. Before attempting to read my explanation below, you might like to have a look at the Help reference "About array formulas and how to enter them". Also, a seach of Google using ' Excel "Array formula" ' will provide a wealth of information including ready-made solutions to common problems. Interpretting my formula. First formula =COUNT(MATCH($J$3:$O$3,$C3:$H3,0)*1) The problem as I interpreted it was to find the number of values in a row in one array that matched the values in the row of another array. Countif() with handle finding a single number in an array but it won't handle all matches in two arrays. This is where an array function is useful. Some of the methods I found in a search of the web were able to solve the problem but, only if the values were in equivalent positions within the array (eg if '1' was the 2nd number in array A, it needed to be the second number in array B). Match() solved the problem in that looked for an equal value in any part of the array. However, Match() on its own returned incorrect result. If you look up Match in Help, youl find that it returns a number relative to the position of the located value in an array (eg 1,2,3,4). Count() fixed that by returning only the number of values returned by Match(). Second part of the formula ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 The problem with the first part of the formula is that it could not be set so that array of numbers from columns J to O did not change as the row changed but did change on column change. The Column() function can be used to work around this. Column() returns a column realting to the cell location (copy '=Column' into a spreadsheet and use the fill handle to see what it does). To use this to relate to a row number is just mathematic Q is the 17th letter of the alphabet. Take away '14' and that leaves row three. Drag it to column R and the return is '4'. Wrap that into an equation using some strings values for the rest of the equation and the result is a reference that changes by a row when as the column changes. Excel does not recognise the above as a valid range reference unless it is enclosed in the Indirect() function. I hope my explanation was understandable. BTW In analysing the equation again, I just discovered that the '*1' at the end of the equation appears superfluous. Remove it. It may help performance a little. Steve *** 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 Totals Please
Paul
Is there a reason you need to have your data laid out that way? It seems to me, your problem may solve easier with a different layout. For example, using the sets of numbers that extend to row 20 in your first two arrays and the third array now built as per your request, Why not simply list the numbers 1,2,3 to whatever down a column just below and to the left of the third array (say p24 for the sake of the exercise)? You formula in Q24 then becomes =COUNTIF($Q$3:$Q$18,$P24). You can drag this without any requirements to complicate your formula. If you don't like the location on the sheet, you can simply copy and paste to a new location on the sheet or to another sheet after checking it works as required. If its a must do layout, I'd be happy to assist but this thread may be getting a little too specific and off topic to be of interest to others. You may like to write to me direct. If you do decide to write direct, please give a brief overview of the aim of the exercise. Please remain patient. I am about to enter a phase of my roster when I may take longer to respond to your questions. Steve "Paul Black" wrote in message ... Hi Steve, Excellent, Thank You Very Much. I have Fiddled Around with my File and have got it All Working Great. As Far as Creating a Big Excel File if there were Many Sets of Numbers, what I will do is to Just have the Formula Input in Cell Q3 and Save the File. Then when I want to Test Against "X" Sets of Numbers I can Copy the Formula Across AND Down as Required, and then Close Without Saving. One Last Question Please. If I Wanted the Total for Each Number of Matches for EACH Set, How would I Create the Formula. Let me Give you an Example. Lets Say we have Matched 10 Sets of Numbers, and the Results are in Columns Q to Z ( Q3:Z12). For the First Set of Numbers we would have :- In Cell AB3, the Total of 0 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=0") In Cell AC3, the Total of 1 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=1") In Cell AD3, the Total of 2 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=2") In Cell AE3, the Total of 3 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=3") In Cell AF3, the Total of 4 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=4") In Cell AG3, the Total of 5 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=5") In Cell AH3, the Total of 6 Matches, the Formula would be =CountIf($Q$3:$Q$12,"=6") Then if we Drag ( Copy ) these Formulas from AB3:AH3 Down we come up with the Same Problem as Before in Regard to Rows and Columns. We would want the Second Set of Numbers Totals to be :- In Cell AB4, the Total of 0 Matches, the Formula would be =CountIf($R$3:$R$12,"=0") In Cell AC4, the Total of 1 Matches, the Formula would be =CountIf($R$3:$R$12,"=1") In Cell AD4, the Total of 2 Matches, the Formula would be =CountIf($R$3:$R$12,"=2") In Cell AE4, the Total of 3 Matches, the Formula would be =CountIf($R$3:$R$12,"=3") In Cell AF4, the Total of 4 Matches, the Formula would be =CountIf($R$3:$R$12,"=4") In Cell AG4, the Total of 5 Matches, the Formula would be =CountIf($R$3:$R$12,"=5") In Cell AH4, the Total of 6 Matches, the Formula would be =CountIf($R$3:$R$12,"=6") I have Tried to Use the Theory of your Formula to Account for this But Without ANY Success. Where as the First One was Rows to Columns, this is Columns to Rows. Achieving this would Finish Off the File Nicely. Thanks in Advance. All the Best. Paul Help with Totals Please From: Steve Paul Because of the amount of cross-checking (six values against six values in each row of your two arrays), I doubt a macro would be much faster. But, I'm often wrong. It may help your performance problem if you turn off automatic calculation when your workbook is opened (turn it back on again when the workbook closes). When you make data changes and are ready, run a manual calculation. Turn off screen updating when it runs. You can automate all this with macros. Just turn on your macro recorder, record each separate action. copy the appropriate parts to "Workbook_Open" and "Workbook_BeforeClose" events. A macro button, keyboard shortcut or strategic addition to another macro can take care of the calculations when you require it happen. The reason the formula worked in two separate halves and not as a whole is beyond my comprehension. My guess is that it is a feature of an array formula work. Before attempting to read my explanation below, you might like to have a look at the Help reference "About array formulas and how to enter them". Also, a seach of Google using ' Excel "Array formula" ' will provide a wealth of information including ready-made solutions to common problems. Interpretting my formula. First formula =COUNT(MATCH($J$3:$O$3,$C3:$H3,0)*1) The problem as I interpreted it was to find the number of values in a row in one array that matched the values in the row of another array. Countif() with handle finding a single number in an array but it won't handle all matches in two arrays. This is where an array function is useful. Some of the methods I found in a search of the web were able to solve the problem but, only if the values were in equivalent positions within the array (eg if '1' was the 2nd number in array A, it needed to be the second number in array B). Match() solved the problem in that looked for an equal value in any part of the array. However, Match() on its own returned incorrect result. If you look up Match in Help, youl find that it returns a number relative to the position of the located value in an array (eg 1,2,3,4). Count() fixed that by returning only the number of values returned by Match(). Second part of the formula ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 The problem with the first part of the formula is that it could not be set so that array of numbers from columns J to O did not change as the row changed but did change on column change. The Column() function can be used to work around this. Column() returns a column realting to the cell location (copy '=Column' into a spreadsheet and use the fill handle to see what it does). To use this to relate to a row number is just mathematic Q is the 17th letter of the alphabet. Take away '14' and that leaves row three. Drag it to column R and the return is '4'. Wrap that into an equation using some strings values for the rest of the equation and the result is a reference that changes by a row when as the column changes. Excel does not recognise the above as a valid range reference unless it is enclosed in the Indirect() function. I hope my explanation was understandable. BTW In analysing the equation again, I just discovered that the '*1' at the end of the equation appears superfluous. Remove it. It may help performance a little. Steve *** 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 Totals Please
Hi Steve,
Funny Enough, thats what I did do Last Night. Thank You for your Time and Effort on Resolving this Issue. It is Not that Important a Task to Warrant Any More Work on. I have Something Now which is Workable. I Tested it with 1,000 Sets of Numbers in the First Array and 100 Sets of Numbers in the Second Array and All was OK. When I get Some Spare Time I will Try and Replicate this Task Using VBA ( Just Starting to Learn ) with SUMPRODUCT Maybe and a Couple of Loops, it will be a Good Project to Help with Learning VBA. Once Again Thanks. Have a Great Weekend. 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 Totals Please
Hi Steve,
One Final Question Please, I am Using the Formulas … Q2 ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Q3 =COUNT(MATCH(INDIRECT(Q$2),$C3:$H3,0)) … and they Work Great. However, I Inserted 5 Extra Lines at the Top of the Sheet and the Formula ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Doesn't Seem to Work Anymore. I have Tried Changing Different things But to NO Avail. 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 Totals Please
Paul
The aim of the function at Q2 is to return the range of the row in the second array containing values to check. So, the manipulation needs to be done to the part of the formula that affects the row numbers of the address range the function returns. The original formula had '-14' in two places. Since you inserted five rows, you need to subtract 5 from 14 to get it to return the new cell address . ="$J$" & COLUMN()-9 & ":$O$" & COLUMN()-9 Depending where this formula now resides (Q2 or Q7), you need to adjust the reference to the formula that will now reside in Q8. Experiment to aid understanding: Open a blank workbook. Enter the formula =Column() at A1 in any sheet. Use the fill handle to drag it across and down the sheet. Study the result. Enter the formula ="A" & COLUMN() at A1 in any sheet Use the fill handle to drage it across and down. Now try the same using ="A" & COLUMN()+1 Steve "Paul Black" wrote in message ... Hi Steve, One Final Question Please, I am Using the Formulas . Q2 ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Q3 =COUNT(MATCH(INDIRECT(Q$2),$C3:$H3,0)) . and they Work Great. However, I Inserted 5 Extra Lines at the Top of the Sheet and the Formula ="$J$" & COLUMN()-14 & ":$O$" & COLUMN()-14 Doesn't Seem to Work Anymore. I have Tried Changing Different things But to NO Avail. 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 Totals Please
Hi Steve,
Thanks for the Explanation and Examples, I Understand how it Works Now. Thank you for ALL your Help and Time on this. Good Luck. All the Best. Paul *** 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 | |||
Pivot Totals: Group totals different from Grand totals | Excel Discussion (Misc queries) | |||
Grand Totals with Nested Sub Totals | Excel Discussion (Misc queries) | |||
how to enter totals and sub totals from receipts into excel. | New Users to Excel | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions |