In article <
f38f9acc-9be1-4a07-b65f-bc8699a836d2n@googlegroups.com>, qazvin2012 @gmail.com says...
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
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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)