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!