Transpose Data

kaushik_birmiwal

Hi All,

I have about 500 files in the following format:

Question No	Question	Response	Comments	Response	Comments
1.1.	What is your name	Kaushik		John
2.1.	What is your age	XX		XX
2.2.	Which school did you attend	Abc		Abc
2.2.1.	What marks did you obtain in school	70%		90%
2.2.2.	Did you attend college	No	Reason for not attending college	Yes

I want the data to transposed in the following format:

1.1.		2.1.		2.2.		2.2.1.		2.2.2.
What is your name	Comments	What is your age	Comments	Which school did you attend	Comments	What marks did you obtain in school	Comments	Did you attend college	Comments
Kaushik		XX		Abc		70%		No	Reason for not attending college
John		XX		Abc		90%		Yes

I have tried multiple approached however, I am unable to crack this. Can someone help me on this one? 

Thanks,

Kaushik

kaushik_birmiwal

hi Philip,

Thank you so much for your response. This is super useful - I can then use Excel for quick formatting.

However can you help me with the updated table as well?

Sharing it again on this message.

Question No	Question	Response	Comments	Response	Comments
1.1.	What is your name	Kaushik		John
2.1.	What is your age	XX		XX
2.2.	Which school did you attend	Abc		Abc
2.2.1.	What marks did you obtain in school	70%		90%
2.2.2.	Did you attend college	No	Reason for not attending college	Yes

Thanks,

Kaushik