Compare two range and return count of exact match
- Thread starter Janci
- Start date
Janci
As You have a new member and You have used ... the last U-word,
then You have to reread Forum Rules
How to get the Best Results at Chandoo.org
There You could get many hints to get responses.
As You have a new member and You have used ... the last U-word,
then You have to reread Forum Rules
Site Rules - New Users - Please Read
Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
There You could get many hints to get responses.
Peter Bartholomew
Well-Known Member
This is a solution, but not necessarily how you want it laid out!
Peter Bartholomew
Well-Known Member
You are in luck 
. The solution uses standard spreadsheet formulas but with defined Names rather than cell references.
The result is stated as an array formula but that is because I use MS365 and everything I write is array formula.
. The solution uses standard spreadsheet formulas but with defined Names rather than cell references.The result is stated as an array formula but that is because I use MS365 and everything I write is array formula.
Peter Bartholomew
Well-Known Member
It is a couple of Named formulas working off the question and answer tables.
It is possible to do the same thing with VBA if you prefer that (see attached).
Either way, to set up for another quiz, overwrite new data into the tables and ensure they have resized to fit the new data set (tables grow automatically but downsizing requires manual intervention).
It is possible to do the same thing with VBA if you prefer that (see attached).
Either way, to set up for another quiz, overwrite new data into the tables and ensure they have resized to fit the new data set (tables grow automatically but downsizing requires manual intervention).
Peter Bartholomew
Well-Known Member
This version completes the list of answers and scores the 10 questions
1. Using arrays with MMULT to sum rows
2. As above but with a function SUMROWS available from a paid addin (MS365 only)
3. Calculated using VBA and triggered by clicking a shape. The Sub calls a Function to perform the calculation.
4. The function may also be called directly from the worksheet as a UDF
All this may be interesting for me but I am not convinced that it gets you any closer to something you can use.
It all depends on what you know already.
1. Using arrays with MMULT to sum rows
2. As above but with a function SUMROWS available from a paid addin (MS365 only)
3. Calculated using VBA and triggered by clicking a shape. The Sub calls a Function to perform the calculation.
4. The function may also be called directly from the worksheet as a UDF
All this may be interesting for me but I am not convinced that it gets you any closer to something you can use.
It all depends on what you know already.