Missing Country Code Please help me with this task

[mota88]

Missing Country Code
Imagine you get a data set from a client that contains addresses from 150 countries all around the world and your task is to verify them, the data is stored in 4 fields – Address Line 1, Address Line 2, City, ZIP code. What you also have available is address verification solution for each country, but the data set does not include the country code. How would you design the logic that would process the data and use the verification components in the most efficient way?
Hint: Running all of those 150 address verification components against each record is not considered efficient.

[oeldere]

You get better help on your question if you add a small excel file, without confidential information.

Please also add manualy the expected result in your file.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.


I think VLookup or Index / Match can solve that problem.

[mota88]

Thank you for you replay oeldere, but I need just the logic for it - How would design the logic that would process the data and use the verification components in the most efficient way
Not in excel file

Many thanks in advance

[oeldere]

Not in excel file

How would you do it if you not do it in Excel.

You are on a Excelforum, for help with Excelproblems.

[mota88]

Yes,
But I need just a simple logic for it and would try to add it by myself.
Just need the most efficient way for doing it
And also running each record is not considered efficient.

[oeldere]

In that case post an small Excel file, without confidential information.

Please also add the expected result in your file.

Then the forummembers can decide what they think is the most efficient way.

[mota88]

Thank you again oeldere
I just need an idea firstly for what would be the most efficient way for doing it - only the idea for now
Thanks!

[AliGW]

Is this some sort of educational assignment?

[mota88]

Is this some sort of educational assignment?

Hello there,

Yes it is something like that
Thank you in advance for your help!

[AliGW]

You need to read up on the VLOOKUP and INDEX MATCH functions to solve the problem. As this is an assignment, this is the only hint we can give you. You will need to work out the logic yourself - that is the crux of the task that has been set. Good luck!

[mota88]

Yes thank you It's something like this
So the lookup value should be Country Code - first column
ZIP Code - second column(range containing the lookup value)
City - third column( the column number in the range containing the return value)
Address Line 1 and 2 - forth and fift column (optianaly specify TRUE for approximate match or FALSE for an exact match)
Please let me know if I got it Right

[mota88]

Also I think firstly I have to combine all of the duplicates City's and ZIP codes
Please let me know if I am on right directions and if you are having any suggestions

[oeldere]

an excel file tells more than 1.000 words (in this case).

[MarvinP]

Hi mota,

I think you need a database of all large city names and their zip codes than has a country code also. Then you could compare the City, Zip in your data against this database.

I've done this for a huge genealogy database and needed to do it in many passes. Problems occur when people misspell the city or the zip. It is also confused by having multiple cities with duplicate names. Thirdly cities change their names from time to time which gives problems. Last is countries or states or zip codes change their boundaries or names.

In general you need a good current database, something like: http://www.fallingrain.com/world/US/ to compare against.

[mota88]

Hi mota,

I think you need a database of all large city names and their zip codes than has a country code also. Then you could compare the City, Zip in your data against this database.

I've done this for a huge genealogy database and needed to do it in many passes. Problems occur when people misspell the city or the zip. It is also confused by having multiple cities with duplicate names. Thirdly cities change their names from time to time which gives problems. Last is countries or states or zip codes change their boundaries or names.

In general you need a good current database, something like: http://www.fallingrain.com/world/US/ to compare against.

Thank you very much for your suggestion MarvinP
I really appreciate it : )
Have a great day ahead!