Simple Transpose, Cell Offset?

Hello! I hope someone will be able to help me! I have been searching for quite some time for a solution, and it seems this forum will help!

I have the results of a survey, and would like to transpose them so that they can be analyzed. Right now, the format looks like this:

A B C
RespondentID Question Answer (header)
Resp1 Question 1 Answer 1
Resp1 Question 2 Answer 2
Resp2 Question 1 Answer 1
Resp2 Question 2 Answer 2 ... (and so on, for 6288 rows)


I'd like it to look like:
A B C
RespondentID Question 1 Question 2
Resp1 Answer 1 Answer 2
Resp2 Answer 1 Answer 2


I have never worked with VBA or Macros before, so I'm trying my way along...

I understand that I have to copy the respondent ID's, skipping the copies, and also transcribe column C. I've come up with this so far:

Sub Recipients()

Range("A2").Select
Selection.Copy
Sheets("Results").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Selection.Offset(27, 0).Select

End Sub

This copies the first respondent's ID to a new sheet, then goes back to the original sheet and jumps to the next respondent. However, if I have it Loop, it pastes the next ID on top of the first ID. How do I code it to skip to the next cell??

If anyone understands what I'm trying to do and can give me a code (as simple as possible?) I would be so grateful!!

Thank you all so much!
~ Maj Kristina

Hi,

You don't actually need a macro. If your example data is in A1:C5 then

E2:
=INDEX(A:A,ROW()*2-2,1)
G2:
=INDEX(C:C,ROW()*2-1,1)

Copy E2 to F2 then Copy E2:G2 down half the number of rows covered by your data.

Regards

Hi,
Yes the formula is different, assuming there are 30 questions

E2:
=INDEX($A:$A,(ROW()-1)*30-28+COLUMN()-5,1)
F2:
=INDEX($C:$C,(ROW()-1)*30-28+COLUMN()-6,1)

copy F2 across to AI2, then copy E2:AI2 down for as many rows as there are IDs.

Regards