Offset and fill

Hi. I'm a relatively new Excel user (at least for more than basic functions).

I am trying to manipulate some data and am having trouble doing it easily.

I have an excel spreadsheet of survey responses as such:

A B
1 Q1
2 A1 X%
3 A2 Y%
4 A3 Z%

5 Q2
6 A1 L%
7 A2 M%
8 A3 N%

Where Q is a question number, A is an anser choice and X,Y,Z represent percent values of respondents who answered int his fashion.

I want to turn it into this:
A B C D
1 Q1 Q2 Q3
2 A1 X% L% P%
3 A2 Y% M% Q%
4 A3 Z% N% R%

While I could copy and paste, the actual spreadsheet is much larger. I am trying to use the offset function and autofill so that
B2= Offset(B2,0,0) and C2=(B2,4,0) [From the table above].

I cannot get Excel to autofill with the row value changing eactime (going from 0 to 4 to 8 to 12, etc.).

Is there any way to do this? Is there another solution to my problem?

Please email if you can help. myusername [at] hotmail [dot] com

Place the following in B2 of Sheet2 (assuming orginal is on Sheet1 and transform is on Sheet2):
=OFFSET(Sheet1!$B$1,MATCH(Sheet2!B$1,Sheet1!$A:$A,0)+ROW()-2,0,1,1)

Then fill down and across as needed.

Clay Ver Valen
Excel help