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!