Avatar of 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!
Microsoft OfficeMicrosoft AccessMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon

do you have a sample data ?

Hi gowflow,

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


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 ?

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

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.


ok working on it no problem just give me sometime as hv to step out. Will b back asap.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

gowflow, thank you very much for your help! This solution worked perfectly!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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