P
Paul
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.
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.