• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare two range and return count of exact match

Janci

New Member
I need to compare 2 ranges and in another cell it should reflect count of exact match.its for find quiz winner,who has answered Many questions correct.
i would be great if I get response .Urgent
 

vletm

Excel Ninja
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.
 

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.
70023
 

Attachments

Janci

New Member
thanks for your response. is this a macro program or simple formula . i want it in macro program so that i can make use for every quiz contest.
 

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).
 

Attachments

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.
 

Attachments

Top