alfamikefoxtrot
asked on
MS Excel -- replace data in a sheet with lookup values from another sheet using VBA
Hi all -- I have some text data stored in a column on an XLS sheet that I need to replace with some other values (numbers) via VBA. I've thought about a few ways of doing this but haven't hit on anything elegant yet. were it not for the fact that the data itself needs to be outright replaced, I'd use VLOOKUP, but that is not an option. I need to do it using VBA too.
I've attached a dummy data file. In this file, sheet "data_sheet" contains my data; column Q4 contains the specific data that I want to replace. Sheet "lookup_sheet" contains a listing of the text values that I want to lookup and their matching numeric values that I want to replace them with on data_sheet. Extending the example, row 4 of data_sheet contains a column Q4 value of "not so important'" which I want replaced with the value 4 (the matching value for "not so important" in column Q4_attribute_value on lookup_sheet). Row 6 contains a Q4 value of "kinda important" which correlates to a numeric value of 5 on lookup_sheet.
I'm using the XLS file as a means to convert data that I'm importing from other sources, so the number of rows in data_sheet will always vary, as will the actual values in each of the columns. That said, the values in Q4 will always be one of the lookup values on lookup_sheet.
This would not be a problem for me in Access, but my skills in Excel are poor at best, so any help with VBA code to do this reliably and repeatably would be GREAT.
Thanks!
dummy-data.xlsx
I've attached a dummy data file. In this file, sheet "data_sheet" contains my data; column Q4 contains the specific data that I want to replace. Sheet "lookup_sheet" contains a listing of the text values that I want to lookup and their matching numeric values that I want to replace them with on data_sheet. Extending the example, row 4 of data_sheet contains a column Q4 value of "not so important'" which I want replaced with the value 4 (the matching value for "not so important" in column Q4_attribute_value on lookup_sheet). Row 6 contains a Q4 value of "kinda important" which correlates to a numeric value of 5 on lookup_sheet.
I'm using the XLS file as a means to convert data that I'm importing from other sources, so the number of rows in data_sheet will always vary, as will the actual values in each of the columns. That said, the values in Q4 will always be one of the lookup values on lookup_sheet.
This would not be a problem for me in Access, but my skills in Excel are poor at best, so any help with VBA code to do this reliably and repeatably would be GREAT.
Thanks!
dummy-data.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome!
Thanks, you too!
Thanks, you too!
ASKER