Help With Unpivoting / Stacking Columns

ZondaZealot

New Member
Joined
Mar 11, 2015
Messages
2
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 NameRecordRespondent IDEmail AddressFirst NameLast NameGenderYear of BirthLike SoccerFavorite Team
Fred Thomas0001 1234fred@gmail.comFredThomas1958YesBrazil
Tom Jackson000212345tom@gmail.comTomJacksonMale1969No
Jack Smith0003123456jack@gmail.comJackSmith1966YesUSA
Lauren Gosney00041234567lauren@gmail.comLaurenGosneyFemale1974YesItaly

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

FORMAT I NEED TO GET RESULTS IN:

Respondent IDQuestionAnswer
1234Gender
12345GenderMale
123456Gender
1234567GenderFemale
1234Year of Birth1958
12345Year of Birth1969
123456Year of Birth1966
1234567Year of Birth1974
1234Like SoccerYes
12345Like SoccerNo
123456Like SoccerYes
1234567Like SoccerYes
1234567Favorite TeamBrazil
1234567Favorite Team
1234567Favorite TeamUSA
1234567Favorite TeamItaly

<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
 
Last edited:

Forum statistics

Threads
1,085,864
Messages
5,386,404
Members
401,997
Latest member
cizwiz

Some videos you may like

This Week's Hot Topics

Top