I have an Excel worksheet which stored a survey outcome of over 1200
members. There are over 30 questions in the survey and the first column
captured the member ID. The problem is 3 of the survey questions that allow
multiple answers, instead the answer for those three questions are captured
in seperate column, the answers are stored in a single column and it makes
it very difficult to analysis those result. As a result I created
addittional columns depending on the total number of the answers available
for that question and assign the result manually to that column. It is very
time consuming and I wonder if I can create a macro to loop through each
member on those three questions and assign the result(s) to the new created
columns.

The format of the existing Excel worksheet is as follow; * denote as
multiple answer allowable
for example: Q2 has 4 answer choices, car, plane, boat and people and in the
order of the answer in the survey
Q3 has 5 answer choices, power point, excel, access,
word and outlook and in the order of the answer in the survey

memberID----Q1-------*Q2-------*Q3---------Q4
123456 yes car power point yes
plane excel
access
word
234578 no plane excel no
boat word
people
784528 yes people power point yes
access
outlook

What I did to the existing Excel worhsheet I changed the column name from
*Q2 to the first available answer which is car for the Q2 and added three
addittional columns, plane, boat and people next to the column "car" and so
on for *Q3. What I want to achieve here is ONE ROW of record for each of the
member survey answer. I need a macro to loop through each member and assign
the survey result to the proper column and delete the "Blank" rows at the
end. Thanks.