We help IT Professionals succeed at work.

Bulk update/recode of survey text responses to numerical values via a lookup table using macros

145 Views
Last Modified: 2017-04-18
My objective is to build a macro either in Access or Excel to be able to convert standard text responses (strongly disagree, disagree, neutral, agree, strongly agree) into their equivalent numerical values (strongly disgree = 1, disagree = 2, neutral = 3, agree = 4, strongly agree = 5)

The source data is from Google survey, which generates these standard text responses on a 5-point scale.
Each survey response would contain around a 200+ unique responses to 30+ questions, most of which would require inputs in this 5-point scale format.

I thought a macro would be the way to go as it would be quite tedious to do a vlookup for each column of responses where there is this 5-point scale. However, I'm not sure if Excel or Access would be the best way to go about this. Greatly appreciate your expert thoughts and solutions!
Comment
Watch Question

gowflowPartner
CERTIFIED EXPERT

Commented:
do you have a sample data ?
gowflow

Author

Commented:
Hi gowflow,

Thanks for your response. Pls find the sample data attached for reference.

Lynn
SampleDataSurvey.xlsx
gowflowPartner
CERTIFIED EXPERT

Commented:
ok please correct me if I am wrong.

You will have data in sheet SurveyData

and you want a macro that will build the sheet NewHeaders based on the info that exist in the other 2 sheets ?

One more thing Can you post more data so the macro that is built can be verified ?

gowflow

Author

Commented:
To confirm:
- data is stored in the sheet SurveyData
- Yes, I wish to have a macro created to build the sheet NewHeaders based on the info that exist in the other 2 sheets, SurveyData and RecodeValues

Pls find the updated file with more data posted in the SurveyData sheet enclosed for your reference.

Thanks!
SampleDataSurveyV2.xlsx
gowflowPartner
CERTIFIED EXPERT

Commented:
ok working on it no problem just give me sometime as hv to step out. Will b back asap.
rgds
gowflow
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
gowflow, thank you very much for your help! This solution worked perfectly!
gowflowPartner
CERTIFIED EXPERT

Commented:
Perfect !!!

I am glad it worked fine for you. Please do not hesitate to let me know in a message if you need help on any issue by posting a link.

Have a nice day
gowflow