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

[PhilipMannering]

Alteryx doesn't really do nested headers, but maybe this is good enough?

[kaushik_birmiwal · Accepted solution]

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