finding postcodes

**SwanyP** · 02-27-2011, 04:55 PM

Hi all,

Hope you can help. I have a data set which contains about 20 000 schools. I have the name of the school and the city. I need to add postcodes/zips for each of these schools. I have tried to match them up with other DBs thta have the postcodes but because of inconsistencies in spelling etc, thi only ever has about a 20% success rate.

SO my question is.... is there any clever automated way of doing this or do I need to hire some help??

Sorry if this should be in programming section, feel free to move it if it should.
Many thanks in advance!!!!

THANKS

**MarvinP** · 02-27-2011, 06:02 PM

Hi Swany and welcome to the forum,

We'd hate to hear you killed yourself because of some stupid ZIP code problem.

It would be helpful if you supplied a small sample of your data, so we could see if we have a good answer.
Click on Go Advanced below the message area and then on the Paper Clip Icon above the message. That will give a window to attach a file.

Example - I need to know if you have the City and State of each school or just a street address. An attached example would show this.

**SwanyP** · 02-27-2011, 06:22 PM

Hi there,
I have uploaded a sample. It has name of school and city (mostly UK). I'm afraid it is a simple and as complicated as that

**royUK** · 02-28-2011, 05:43 AM

Where are the postcodes stored?

**SwanyP** · 02-28-2011, 09:08 AM

Thats just it. Most of them are not stored. the ones that are, I have matched up using a merge tables wizard (is there any in-built way of doing this BTW). So I need them to be scraped from Google or something!!
Thanks

**royUK** · 02-28-2011, 09:16 AM

If it's possible it would take forever

**MarvinP** · 02-28-2011, 01:21 PM

Hi SwanyP,

This kind of problem is a killer as it seems too big

.
Find the attached where I've helped a little.

I searched the net and found a website that is a directory to UK schools. I typed in a school name and looked at the URL for the search. Then I studied the URL and how it searched using the school name. It really just replaced spaces with "+".

You have the school names and Excel can replace space with "+" and can also follow URLs.

I created a little Macro called "Find That School" that put the URL search string in D1 and then opened the web site so you could look. Then you will need to click on the "Info" link, copy the ZIP into the clipboard and come back to Excel to Paste it in, where you like.

I've triggered this macro by double clicking on Column E to the right of the name.

I believe you can shorten your list so it has no duplicates and this might trim your list down to 5,000 schools or less.

This doesn't make your job easy but easier.

hope this helps.