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!

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!

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!

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!

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!

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!

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!