Link to home
Start Free TrialLog in
Avatar of Member_2_8007347
Member_2_8007347

asked on

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

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!
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

do you have a sample data ?
gowflow
Avatar of Member_2_8007347
Member_2_8007347

ASKER

Hi gowflow,

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

Lynn
SampleDataSurvey.xlsx
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
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
ok working on it no problem just give me sometime as hv to step out. Will b back asap.
rgds
gowflow
ASKER CERTIFIED SOLUTION
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
gowflow, thank you very much for your help! This solution worked perfectly!
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