Search code examples
exceldynamically-generatedoffice-2013excel-2013vba

Excel 2013: Generate list meeting all requirements

I need to create a spreadsheet that randomly draws a winner that has correctly answered all questions.

Verifying Answer

I've added a Column 'Correct' which verifies the answer was properly answered. It returns QuestionID if correct and 'Incorrect' if FALSE.

I have another table with the answers to verify the database results.

  • 9747 | Wrong | Correct | Wrong
  • 9748 | Correct | Wrong | Wrong
  • 9749 | Wrong | Wrong | Correct
  • 9751 | Correct | Wrong | Wrong
  • 9752 | Wrong | Wrong | Correct
  • 9753 | Wrong | Wrong | Correct

Pull Possible Winners

I need to get all PersonID that have properly answered all the questions. If they incorrectly answer a question they are disqualified. Also if they failed to answer a question they are disqualified.

TableWithData

Solution

  • I tried to follow the layout of visible in the sheet image and compiled my own sheet: http://www.bumpclub.ee/~jyri_r/Excel/Answers_to_quiz_correct_wrong.xls Blocks with additions are marked with light green, error situations with yellow.

    Defined C1:C200 as PersonList.

    All formulae are in row 2, if not stated otherwise.

    Column J: correct answers selected by 

    =IF(AND(E2="Right",F2="Right",G2="Right"),B2,"")

    Column L: compiled unique list of participants by array formula (Shift-Control-Enter, curled brackets added by Excel itself, not by a user): 

      {=INDEX(PersonList,MATCH(0,COUNTIF($L$1:L1,PersonList),0))}

    Column M: list of answers for the first question found (the first means position in a table):

    =MATCH($L2,$C$2:$C$2000,0)+1

    Column N: list of answers for other questions, copied further to columns O:R

    =MATCH($L2,INDIRECT("$C"&M2+1&":$C$2000"),0)+M2

    Column S: correct answer sought by a row number in column M. If the question wwas answered properly, question code will be displayed. The formula is copied to columns T:X.

    =(INDEX($J$1:$J$200,M2))

    Column Y: Winners and DQ. Array formula again:

    {=IF(SUM(IF(S2:X2="",1,0))=0,"Winner","DQ")}

    Now AutoFilter can be applied.