Problem in excel - possible MACRO solution ?

[/-_-b]

I got this file with the trivia questions and answers.

The sheet in excel looks something like this :

A column :
Question 1
a) Answer 1
b) Answer 2
c) Answer 3
d) Answer 4

Question 2
a) Answer 1
b) Answer 2
....

B column :
+ mark in the row where the answer to the question is right.


My question is : can I make a new sheet with the question numbers and the
right answers only ? It should look something like this :

1 a
2 b
3 d
4 c
5 d
....

Ty all in advance !

Marko

 

[Ken Johnson]

I got this file with the trivia questions and answers.

The sheet in excel looks something like this :

A column :
Question 1
a) Answer 1
b) Answer 2
c) Answer 3
d) Answer 4

Question 2
a) Answer 1
b) Answer 2
...

B column :
+ mark in the row where the answer to the question is right.


My question is : can I make a new sheet with the question numbers and the
right answers only ? It should look something like this :

1 a
2 b
3 d
4 c
5 d
...

Ty all in advance !

Marko

Hi Marko,

This formula on the other sheet might work (assuming sheet with
Questions and Answers is Sheet1)...

=ROWS($1:1) &CHOOSE(MATCH("+",INDIRECT("Sheet1!B"&MATCH("Question
"&ROWS($1:1),Sheet1!$A:$A,0)+1&":B"&MATCH("Question
"&ROWS($1:1),Sheet1!$A:$A,0)+4),0),"a","b","c","d")

Ken Johnson

 

[Ken Johnson]

Hi Marko,

This formula on the other sheet might work (assuming sheet with
Questions and Answers is Sheet1)...

=ROWS($1:1) &CHOOSE(MATCH("+",INDIRECT("Sheet1!B"&MATCH("Question
"&ROWS($1:1),Sheet1!$A:$A,0)+1&":B"&MATCH("Question
"&ROWS($1:1),Sheet1!$A:$A,0)+4),0),"a","b","c","d")

Ken Johnson

If the email has broken the formula up make sure that there is a space
inside the speech marks after the word "Question" .
ie "Question " not "Question"

Ken Johnson

 

[Ken Johnson]

That probably won't work. Sheet1 more than likely has question text,
not just question-space-number.

Try this macro instead...

Public Sub CorrectAnswers()
Application.ScreenUpdating = False
Dim I As Long, J As Long
Do While I < WorksheetFunction.CountIf(Worksheets(1).Range("B:B"),
"+")
J = J + 1
If Worksheets(1).Range("B" & J).Value = "+" Then
I = I + 1
Worksheets(2).Range("A" & I).Value = _
"'" & I & " " & Left(Worksheets(1).Range("A" & J).Value, 1)
End If
Loop
End Sub

Ken Johnson