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:

Full Name	Record	Respondent ID	Email Address	First Name	Last Name	Gender	Year of Birth	Like Soccer	Favorite Team
Fred Thomas	0001	1234	fred@gmail.com	Fred	Thomas		1958	Yes	Brazil
Tom Jackson	0002	12345	tom@gmail.com	Tom	Jackson	Male	1969	No
Jack Smith	0003	123456	jack@gmail.com	Jack	Smith		1966	Yes	USA
Lauren Gosney	0004	1234567	lauren@gmail.com	Lauren	Gosney	Female	1974	Yes	Italy

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

FORMAT I NEED TO GET RESULTS IN:

Respondent ID	Question	Answer
1234	Gender
12345	Gender	Male
123456	Gender
1234567	Gender	Female
1234	Year of Birth	1958
12345	Year of Birth	1969
123456	Year of Birth	1966
1234567	Year of Birth	1974
1234	Like Soccer	Yes
12345	Like Soccer	No
123456	Like Soccer	Yes
1234567	Like Soccer	Yes
1234567	Favorite Team	Brazil
1234567	Favorite Team
1234567	Favorite Team	USA
1234567	Favorite Team	Italy

<colgroup><col><col><col></colgroup><tbody>
</tbody>

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.