Help With Unpivoting / Stacking Columns

[ZondaZealot]

Hi, I am not a frequent user of VBA, but have been tasked with developing a macro to re-structure survey results for reporting purposes. I was really hoping someone could help me with the code since I'm honestly at a loss at this point. First, I should reference my question is somewhat along the lines of this post, with some variation: http://www.mrexcel.com/forum/excel-questions/399106-transform-table-into-list-how-unpivot-table.html

As for my case: I will be receiving a spreadsheet with about 1,000 rows of data, each regarding a unique respondent. The first 6 columns (A-F) will contain information identifying that person. The next roughly 200 columns (G onward) will each have a question as the header, with each respondent's answer below. For our reporting software, I need to get this data into 3 columns. One having the Respondent IDs, the second column containing the question asked, and the third containing the answers. Blanks should be copied even if no one answered the question. Here's some dummy data in examples of the table I'll receive and what I hope to see (question columns are unshaded to tell them apart easier, and again, there won't be 4, but about 200 of these columns):

FORMAT OF FILE I RECEIVE:

[TABLE="width: 1013"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Full Name[/TD]
[TD]Record[/TD]
[TD]Respondent ID[/TD]
[TD]Email Address[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Gender[/TD]
[TD]Year of Birth[/TD]
[TD]Like Soccer[/TD]
[TD]Favorite Team[/TD]
[/TR]
[TR]
[TD]Fred Thomas[/TD]
[TD]0001 [/TD]
[TD]1234[/TD]
[TD]fred@gmail.com[/TD]
[TD]Fred[/TD]
[TD]Thomas[/TD]
[TD][/TD]
[TD]1958[/TD]
[TD]Yes[/TD]
[TD]Brazil[/TD]
[/TR]
[TR]
[TD]Tom Jackson[/TD]
[TD]0002[/TD]
[TD]12345[/TD]
[TD]tom@gmail.com[/TD]
[TD]Tom[/TD]
[TD]Jackson[/TD]
[TD]Male[/TD]
[TD]1969[/TD]
[TD]No[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack Smith[/TD]
[TD]0003[/TD]
[TD]123456[/TD]
[TD]jack@gmail.com[/TD]
[TD]Jack[/TD]
[TD]Smith[/TD]
[TD][/TD]
[TD]1966[/TD]
[TD]Yes[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Lauren Gosney[/TD]
[TD]0004[/TD]
[TD]1234567[/TD]
[TD]lauren@gmail.com[/TD]
[TD]Lauren[/TD]
[TD]Gosney[/TD]
[TD]Female[/TD]
[TD]1974[/TD]
[TD]Yes[/TD]
[TD]Italy[/TD]
[/TR]
</tbody>[/TABLE]

FORMAT I NEED TO GET RESULTS IN:

[TABLE="width: 301"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Respondent ID[/TD]
[TD]Question[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Gender[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Gender[/TD]
[TD]Male[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Gender[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Gender[/TD]
[TD]Female[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Year of Birth[/TD]
[TD="align: right"]1958[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Year of Birth[/TD]
[TD="align: right"]1969[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Year of Birth[/TD]
[TD="align: right"]1966[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Year of Birth[/TD]
[TD="align: right"]1974[/TD]
[/TR]
[TR]
[TD]1234[/TD]
[TD]Like Soccer[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Like Soccer[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Like Soccer[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Like Soccer[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Favorite Team[/TD]
[TD]Brazil[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Favorite Team[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Favorite Team[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]Favorite Team[/TD]
[TD]Italy[/TD]
[/TR]
</tbody>[/TABLE]


If anyone can give me VBA code that will do this, it would be an absolute life saver. Thank you so much for your time and consideration!

- Mark

 

[Derek Brown]

Hello and welcome to The Board.
Have a look at the following to see if it provides the answer that you are looking for:
https://www.youtube.com/watch?v=mxmfOoxIVCQ
https://www.youtube.com/watch?v=JfuFGMfgvfY

 

[ZondaZealot]

Hello and welcome to The Board.
Have a look at the following to see if it provides the answer that you are looking for:
https://www.youtube.com/watch?v=mxmfOoxIVCQ
https://www.youtube.com/watch?v=JfuFGMfgvfY

This is a spectacular trick! Thank you so much for helping me out on this.