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

[Anon_S]

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!

 

[shinigamilight]

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 .

 

[Anon_S]

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​

 

[Anon_S]

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

 

[shinigamilight]

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.

 

[Anon_S]

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

 

[Fluff]

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.