Distance calculation and filtering

[greg1075]

Hello again,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I have on a sheet a list of cities (in MN, WI, IL and both Dakotas) where physicians travel to do exams. Users can filter those cities. On a second hidden sheet, I have a database of all US cities, their state, zip code and latitude/longitude coordinates.<o></o>
I need to code a cell on the first sheet so that users can enter any zip code and Excel returns a list of all US cities within 150 miles in an adjacent cell. <o></o>
I already have the function to calculate the distance (in miles) between two sets of coordinates:<o></o>

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *3958.756<o></o>

My thoughts: The user-input zip will need to vlookup the zip column + latitude / longitude columns of the database; run the function above using that set of coordinate as Lat1 and Long1, and calculate all results using the coordinates for all the other US cities as Lat2 and Long2 (33,178 cities total) - and finally return the names of all the cities that fall within 150 miles of the original zip code in the cell adjacent to the one where the user input the zip code<o></o>
How the heck would you code that?<o></o>
Thank as always. this forum is a precious source of help!

 

[Gerald Higgins]

IF the function you have posted actually does calculate distance satisfactorily (I haven't checked it myself, I just assume it does), then can you apply it to each of the 33,000 cities in your database, using the user input zip code as 1 set of co-ordinates, and the relevant city as the other ?
This will give you the distance of each of the 33,000 cities to the user input zip code.
I'm conscious that file size might become an issue here, so maybe try it on a small extract of the list first.

Then, there are various ways to extract selected cities from your list.
One way would be to use the Data Sort function, to sort all 33,000 cities, using the distance from the user-input zip as the primary sort key, and return the first 10, or 100, or however many you wanted, to the user input page.

VBA might be useful here as well, but I'm not an expert in that myself.

 

[greg1075]

IF the function you have posted actually does calculate distance satisfactorily (I haven't checked it myself, I just assume it does), then can you apply it to each of the 33,000 cities in your database, using the user input zip code as 1 set of co-ordinates, and the relevant city as the other ?
This will give you the distance of each of the 33,000 cities to the user input zip code.
I'm conscious that file size might become an issue here, so maybe try it on a small extract of the list first.

Then, there are various ways to extract selected cities from your list.
One way would be to use the Data Sort function, to sort all 33,000 cities, using the distance from the user-input zip as the primary sort key, and return the first 10, or 100, or however many you wanted, to the user input page.

VBA might be useful here as well, but I'm not an expert in that myself.

I have actually adjusted the database to show only the relevant states (5 for now) in order to reduce the file size and calculation time.

Yes, I was wondering about a VBA solution but will look into the options you suggested. I do believe the function is accurate. Thanks!