Return the Questions Answered Incorrectly on a Quiz

[joshnelsontn]

Hello,

I have a test I've created for new hires, and I need to identify which questions are answered incorrectly based off an answer column.

Question:	Result:
1	correct
2	correct
3	wrong
4	correct
5	not answered
6	wrong
7	wrong
8	correct
9	correct
10	not answered

<tbody>
</tbody>

=INDEX(A:A,MATCH("wrong",B:B,0)) will return "3", but my desired output will be "3, 6, 7" (or something similiar).

Any ideas?

Thank you!!

 

[Weazel]

maybe....

=IFERROR(INDEX($A$1:$A$10,SMALL(IF($B$1:$B$10="wrong",ROW($A$1:$A$10)-ROW($A$1)+1),ROWS($D$1:D1))),"") control shift enter

assumes the question is in column A and the result is in column B

 

[hiker95]

joshnelsontn,

How about this?

Excel 2007
	A	B	C	D	E
1	Question:	Result:		wrong	3
2	1	correct		3
3	2	correct		6
4	3	wrong		7
5	4	correct
6	5	not answered
7	6	wrong
8	7	wrong
9	8	correct
10	9	correct
11	10	not answered
12
Sheet1

Cell Formulas
Range	Formula
E1	=COUNTIF(B:B,D1)
D2	{=IF(ROWS($D$2:D2)<=$E$1,INDEX($A$2:$A$11,SMALL(IF($B$2:$B$11=$D$1,ROW($D$2:$D$11)-ROW($D$2)+1),ROWS($A$2:A2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the array formula in cell D2 to range D3:D11

 

[L. Howard]

Hi there,

Maybe either of these may suit.

First code you run and enter in the Input Box what answers you want listed in column F, "wrong", "correct" or "not answered"

The second code list all answers in columns E, F, G.

Regards,
Howard

Option Explicit

Sub WrongCorrectNoAns()

Dim myfind As String
Dim c As Range
[F:F].ClearContents
myfind = Application.InputBox(prompt:="Answers", Title:="Range Select", Type:=2)

For Each c In Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
  If c = myfind Then
     c.Offset(0, -1).Copy Range("F" & Rows.Count).End(xlUp)(2)
  End If
Next

End Sub

'//***************************

Sub AllAnswers()

Dim myfind As String
Dim c As Range
Dim MyArr As Variant

[E:G].ClearContents

MyArr = Array("Correct", "Wrong", "Not answered")

Range("E1").Resize(, 3) = MyArr
              
For Each c In Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row)
 If c = "correct" Then
     c.Offset(0, -1).Copy Range("E" & Rows.Count).End(xlUp)(2)
   ElseIf c = "wrong" Then
       c.Offset(0, -1).Copy Range("F" & Rows.Count).End(xlUp)(2)
   ElseIf c = "not answered" Then
       c.Offset(0, -1).Copy Range("G" & Rows.Count).End(xlUp)(2)
  End If

Next

End Sub