Postal Code Validation in Excel

[میثم مقدم نیا‎]

Hi,
Courtesy and respect for the service of all dear friends and esteemed members

Well, we have a file that has about forty thousand rows, we have the name of the city and the postal code

We need to know if the postal code of that city is correct or not

We also have a list containing the first three standard digits of the postal code of each city

I need the function to be such that

If the name of the city was, for example
Tonekabon
Corresponding zip code
Or 481 or 483 or 484

[Philip Herlihy]

In article <f38f9acc-9be1-4a07...@googlegroups.com>, qazvin2012
@gmail.com says...

Off the top of my head (and I'm afraid I don't have time to work out the
details) this is a possible outline solution:

* You will need to be able to look up values in your postcode list from cells
in your main list. So the two lists could be separate worksheets in the same
file, or you could look up one file from another, though there may be a
performance overhead with the latter. (One-off: who cares?)

* Add a column to your main list with a formula from the "Lookup & Reference"
group (could be VLOOKUP) which returns the City name corresponding to the
postcode from the row. (There is a one-to-many relationship between City name
and postcode.) So if your main data includes "Sleepyville" and "987" then
return the string (city) associated with 987 in the postcode list. This should
be "Sleepyville" but it might be "Sleepiville"; it could be a minor typo or one
of them could be plain wrong.

* Add a futher column to your main data list (you're working with a copy,
right?) to compare the two versions of the City name using one of the functions
from the Text group of functions, optionally converting both to the same case
beforehand if case doesn't matter. EXACT() will return TRUE or FALSE; you may
be able to cater for truncated but otherwise correct names by comparing
characters only up to the LEN() of the shorter of the two, and there may be
other dodges you can pull if you want to get fancy.

* Finally, sort the main data sheet by the TRUE/FALSE column to gather all the
errors/mismatches into adjacent rows for ease of correction or reporting.

There will be other solutions, and very likely better ones. Good luck.

--

Phil, London