Need help with Excel macro to organize a messed up survey

Question

I have been trying to write a macro (in steps) to organize the results of a very poorly designed survey, but I am having very little luck.

Here is a sample of what I have:

Here is a sample of what I need:

I am running into several problems, one of which is that not all of the 15 questions on the survey had to be answered which makes looping through the results in a smooth fashion difficult.

An even bigger problem (tied to the previous issue) is that 3 of the 15 questions on the survey were "Select All That Apply" type questions, and every selection was recorded as a separate answer, but with the same number. For example question 10 had 11 possible selections which a user could choose as many or as few of as they wanted. If they selected the 1st and 3th options of question 10 the result would look like rows 3 and 4 of my What I have sample.

My What I need sample shows that I need all the questions in columns and all the respondent numbers in their own row, with the long answers from a respondent under their respective number.

The ID column from the What I have sample is not needed in the final product, but I have left it in the results for now thinking it might somehow help sort this mess out.

I am wondering if I should just go back to the person who gave this to me and tell them I'm sorry but I cannot do anything with this since it is so messed up. If you think it is possible to fix these survey results please give me some pointers (be detailed, I don't have much experience with VB Script).

Any other comments, thoughts, or suggestions are also welcome.

Answer 1

I have years of experience with VBA in Excel and Access 2003 and 2010, and I can tell you this would not be fun to process in Excel; based on the structure of the survey results, I would highly recommend importing this into Access (if you have it) and running SQL queries to slice and dice the data. It's really in the format of a database table, it even has a primary key (ID).