Need Distance Between Zip Codes

I have two columns, zipA and zipB. There are over 7000 rows and I need to calculate the distance between zipA and zipB for each row (so the result should be over 7000 rows of distances (in miles)). I posted this in the programming board because I figured it would require some. I don't have any add-ons.

Can you show a better example of the current layout and an example or 2 of expected results?

Possibly attach a sample workbook?

Please see attached. There are in total about 7,500 rows. The column I need filled is C, "Distance_Between_(m)"

Where's the table of distances? Where's the raw data coming from?

How are the zip codes made up?

I work for an auto auction company. The zip codes are from one dealer to another. I have dealer name, address, zip, state ,and a few other things. I just included the zips because I figured that is all that is prudent. Basically, I was given a list of zip codes and I need to find the distance between the two.

Originally Posted by Kenneth Hobson

Depends on the country and site you use for lookup if you are doing a web lookup.

See my post at MrExcel for an example.

They will all be US zips.

Macs don't support VBA, right?

My personal computers are macs, but I use Windows machines at work.

Driving distance or airline distance?

Although driving distance would be ideal, I'm more than willing to accept bird-flight distance because I realize it would probably be easier.

You can get lat/lon of zip codes at http://www.census.gov/geo/www/gazett...es2k.html#zcta

This function returns the central angle between two lat/lons:

Please Login or Register  to view this content.

For example,

Please Login or Register  to view this content.

The formula in H2 and down is =3960 * CentralAngle(B2, C2, E2, F2) for distances in statute miles.

Thank you. I'll give it a whirl and let you know!

I am a VBA newb. I formatted my sheet exactly like your example above and copy/pasted the code you supplied into the Visual Basic Editor. When I try to recall the function in the worksheet the error says, "the formula contains unrecognized text."

Post the workbook.

Well......I came into work this morning and it works now!!! Thank you so much!

To answer the question in your PM, 3960 is roughly the mean earth radius in statute miles.

CStyle,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.