Help with Totals Please

[Paul Black]

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

 

[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]

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

 

[Steve]

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]

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

 

[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

 

[Paul Black]

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

Re: 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

 

[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

 

[Paul Black]

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

Re: 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

 

[Steve]

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]

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

 

[Paul Black]

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

 

[Steve R]

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]

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