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!