Searching points within a bounding circle in excel 2

[waughe]

I am trying to find points in an excel database that lie within a circle with a 50 mile radius. I have over 100,000 points in the database all given in lat/long form. The center of the circle will be lat/long (x,y). How do I write a macro that finds the points bounded by this formula?

 

[cloa]

Better to post in the Engineering spreadsheets subforum

http://www.eng-tips.com/threadminder.cfm?pid=770

 

[MortenA]

Your database contains a lot of points (referencea) with data in the lat/long form - and you want to select those where the distance to another reference point is less than X miles?

First make sure that your coordinates uses the same units as your criteria. Then use pythagoras to calulate the distance then select if<X.

You dont even need a macro. Just add the calculation in the next collum and use conditional formatting to highlight the matching points. If you wish to remove all non match, then write a macro, do the calc and erase all the non match. Use "record macro" to quickly determine the coding for navigation and deleting a row.

Best regards

Morten

 

[DRMABZ]

Just to add to the very good suggestion.

You can rap your distance calculation into an "if" function and have it return 0 or a 1 e.g. =1 if within your distance (circle). You can then use the filter function to produce a list, which if required could be copied into another sheet. The filter function will be very useful for large non-sequential lists.