Need help reformatting data, what formula should I use?

[mejatom]

Hello, this is my first time posting on this forum so please forgive me for any issues in this post.

I have survey data collected from a form. Because of the platform we are using, the responses are displayed similarly to the table on the left. Each participant's response spans multiple rows. I am trying to reformat the data into a sheet that looks like the table on the right, with each participant in one row, and the questions in a fixed order.

My issue is figuring out what formula should go in column F, G, H, and I. As you can see in the left table, the order of the questions (Birthday, address etc.) is shuffled for each participant. Hence I'm not sure how to use something like OFFSET and INDEX to do this. Because there are multiple rows of each participant, I'm also not sure how to use VLOOKUP for this.

Would appreciate any help!

 

[James006]

Hi,
For cell F2

=OFFSET($A$2,((ROW()-2)*4)+COLUMN()-6,2)

 

[mejatom]

Hi,
For cell F2

=OFFSET($A$2,((ROW()-2)*4)+COLUMN()-6,2)

Hi, thank you for your response!!
Yes, this code indeed works for cell F2, but I'm trying to come up with a generalisable formula for the entirety of column F. (In the actual data set I have 63 participants, all of which have their questions in a random order) So this formula must work for John as well.

 

[James006]

Quite obviously, you have not tested it ...

The formula is built to be copied down and to the right ...

By the way, regarding Column E, starting in cell E2, you can have

=OFFSET($A$2,((ROW()-2)*4),0)

 

[mejatom]

Quite obviously, you have not tested it ...

The formula is built to be copied down and to the right ...

By the way, regarding Column E, starting in cell E2, you can have

=OFFSET($A$2,((ROW()-2)*4),0)

Oh, I have tested it! Am I doing something wrong? Because the Birthday and Address rows on the left are in random order, I don't think I can use the offset function.

 

[mejatom]

View attachment 88384
Oh, I have tested it! Am I doing something wrong? Because the Birthday and Address rows on the left are in random order, I don't think I can use the offset function.

Apologies, I didn't change the date format here, but yes the formula pulls the address for John (Since it's the first question John answers) instead of Birthday as it should be.

 

[James006]

Well you can add some coherence to your database in Columns A to C with a Sort by Name and then by Question ...

 

[awoohaw]

If your worksheet is really big the OFFSET function may cause it to slow down.
The following does not use volatile functions:

mr excel questions 17.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	Question	Answer			Birthday	Address	Phone Number	Fax Number
2	Mary	Birthday	2000-08-03		Mary	2000-08-03	ABC Road	235	584
3	Mary	Address	ABC Road		John	2000-03-05	XYZ Road	456	314
4	Mary	Phone Number	235
5	Mary	Fax Number	584
6	John	Birthday	2000-03-05
7	John	Address	XYZ Road
8	John	Phone Number	456
9	John	Fax Number	314
10
Sheet6

Cell Formulas
Range	Formula
E2:E3	E2	=INDEX($A$2:$A$9,((ROW(E2)-2)/0.25+1),MOD((COLUMN(E2)-5),3)+1)
F2:I3	F2	=INDEX($C$2:$C$9,MATCH($E2&F$1,$A$2:$A$9&$B$2:$B$9,0),1)

 

[mejatom]

Thank you both so much!

@James006, I'm not sure why I didn't think of that earlier! I've done that and got it working, haha. Thank you!

@awoohaw, I will test this out in the future if needed, thank you very much!

 

[James006]

Glad to hear you have managed to solve your problem