how to use marco/vba or any formula to replace values based on a map table

Anon_S

New Member
Joined
Feb 19, 2023
Messages
4
I have a table with three columns.
Name , city and country. The city names are in codes, I have a mapping for the city codes to their full names.
But since the data has over 10,000 rows and more than 50 different states, it is difficult to do find and replace.
Can you please suggest an alternative to use vba code or any other feasible way to do replace all the codes with the city full names.
Attaching a sample code with the query.
Thank you!

1676810656963.png
 
You didn't tell us what's the input and what's the output? Where will the code lookup for the values to replace with? It looks like the first 2 columns is what you get and you need to add the 3rd column .
 
Upvote 0
I have a table with three columns.
Name , city and country. The city names are in codes, I have a mapping for the city codes to their full names.
But since the data has over 10,000 rows and more than 50 different states, it is difficult to do find and replace.
Can you please suggest an alternative to use vba code or any other feasible way to do replace all the codes with the city full names.
Attaching a sample code with the query.
Thank you!

View attachment 85736

You didn't tell us what's the input and what's the output? Where will the code lookup for the values to replace with? It looks like the first 2 columns is what you get and you need to add the 3rd column .
Hi,
The input is View attachment 85736
The output should be : The codes for city have been replaced with their full name and there are >50 such cities in the dataset
PS: this is just a sample for random city and countries, dont look at the cities and country logically
Name​
City​
Country​
Adam​
London​
UK​
Mikasa​
Amsterdam​
Australia​
Eren​
Delhi​
India​
Levi​
Mumbai​
Russia​
Historia​
Kolkata​
USA​
 
Upvote 0
You didn't tell us what's the input and what's the output? Where will the code lookup for the values to replace with? It looks like the first 2 columns is what you get and you need to add the 3rd column .
Also the mapping can be present in another sheet like so:
DEL Delhi
MUM Mumbai
KOL Kolkata
but more so i want to use it directly in the code because we need to use the code of multiple different sheets
 
Upvote 0
but more so i want to use it directly in the code because we need to use the code of multiple different sheets

This isn't gonna work unless you have every single code and their respective cities in a specific sheet. Good luck.
 
Upvote 0
This isn't gonna work unless you have every single code and their respective cities in a specific sheet. Good luck.
Okay , I can put the mapping into another sheet
City Code City Name
DEL Delhi
MUM Mumbai
KOL Kolakata
and so on...
Can we have a code to look for DEL in the data set and replace it with Delhi and so on and so forth for the remaining city codes as well
 
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: how to use marco/vba or any formula to replace values based on a map table
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top