compare data ranges

  • Thread starter Thread starter Charles Puzzanchera
  • Start date Start date
C

Charles Puzzanchera

Greetings. I am not sure is this subject line works well but here goes. Here
is my task.

I have a worksheet ("key") that stores correct answers for a quiz. The data
range is B2:K2. I have a second sheet ("answers") that contains the
responses to the quiz items from multiple participants. In both sheets,
answers are text and are always in caps (e.g., YES/NO, rather than yes/no).
I would like to set up a third sheet ("results") that tracks
correct/incorrect answers for each respondent as follows. If the answer is
correct, then the result is still in caps; if the answer is incorrect, then
the text of the answer provided by the respondent is converted to all lower
case. One of the nuances is that the answers can be submitted in a different
order, depending on the respondents. So I need to compare the submitted
answers to the entire answer key range. Each question contains a unique set
of dichotomous items, i.e., there are no instances of the same answer
appearing more than once.

I am not sure how to approach. I have made several missteps with hlookup and
match. I know the answer is out there somewhere...

TIA,
chaz
 
Here's one pitch which might help achieve what you're after?

Let's take a small sample set-up to illustrate

Assume you have set-up:

In sheet: Key (in B1:E2)
------------------------------
Q1…...Q2.....Q3……........Q4
YES….NO....MAYBE...POSSIBLY

The range B1:E2 is named: AnsKey

The above is your "master" answer-set

And you have also set-up

In sheet: Answers (in B1:E3)
----------------------------------
Q1.....Q2......Q3......Q4 (<< these could be labels or actual questions)
YES...NO....YES...MAYBE (<< sample responses typed-in)
Q2.....Q4......Q1......Q3 (<< this row3 is hidden after set-up)

Row3 above is where you can randomize the order of the questions
according to your set of Q&As in sheet: Key
i.e. the Q2.....Q4......Q1......Q3 in row3 refers to/matches those
in your answer-set in sheet: Key

Row3 will be presumed *hidden* after set-up

In sheet: Results (in B1:E2)
----------------------------------

Q1....Q2....Q3....Q4
yes...no....YES...MAYBE (<< returns in row2 via formula below)

Put in B2:
=IF(HLOOKUP(TRIM(Answers!B3),AnsKey,2,0)=TRIM(Answers!B2),TRIM(Answers!B2),L
OWER(TRIM(Answers!B2)))

Copy B2 across to E2

B2:E2 will return the results of correct answers in the same (i.e. upper)
case
while incorrect answers will be returned in lower case

TRIM() is used quite liberally to make the formula more robust,
just in case there's any inadvertent typing in of extra spaces
(via spacebar key) in the answers, since we're matching text.
 
Back
Top