Minimum Distance

**Maddogwoof** · 07-20-2011, 05:03 PM

Hello,

I am trying to write a vba function that finds the smallest distance between two large lists of gps coordinates. I already have a basic formula for calculating each of these, but I need to find this efficient function for determining the next closest gps coordinate and by how much. I have done a little bit of vba programming, though I have done more java in the past. I know I will need some type of loop where it looks to determine whether the newly calculated value is smaller than the current running minimum and if not, skip to the next one. I have some rough "pseudocode" of what i'm looking for.

minDistance(range,originalCoords)
define min double = 0
begin loop
dist = originalCoords - nextCoords \\The actual calculation is a little more
if dist < min \\complicated but it should work
dist = min
loop
end loop

I know there's probably a lot missing, including some reference that stops the loop when it hits the last set of coordinates to calculate and something to return the min value. The value nextCoords would be my next listed coordinates to calculate the distance between from the original. Also, I have one cell for latitude and one for longitude, if that is any help.
Any feedback and help would be greatly appreciated!
Thanks!

P.S. if this is listed on another forum topic already, let me know and I will go to there for help!

**shg** · 07-20-2011, 05:57 PM

Welcome to the forum.

How large are the two lists?

All you want to now is the minimum distance, not the nearest point?

You want to calculate great-circle distance?

Post a workbook with some data.

**Maddogwoof** · 07-21-2011, 09:23 AM

The lists are about 400 locations in total and i'm trying to find the minimum distance to the next coordinate for each. I am using the great-circle distance formula for each of these but there is 399 calculations for each 400 locations, so that's why I feel some vba programming is necessary. Here is an attached worksheet with a sample of 5 locations, using a rudimentary method to find each of the minimum, but with 400 locations, excel runs out of room and this would take quite a while to accomplish.

test min dist.xls

Update: I ended up making 400 macros (yes, it took me 5 hours, but I got it done) and got my data, but I still want to know how to write a function in vba to do this for me because I will definitely be using this kind of method in future research of my own.

Update 2: I started looking further into VBA and I have come up with some code that seems like it should work. Here it is:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
Dim Index As Integer, Min As Double, Dist As Double
Index = 3
Min = 500
While Index < 404
Index = Index + 1
Dist = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) * Cos(WorksheetFunction.Radians(Lon1 - Worksheet.Cells(Index, 3)))) * 3958.756
If Dist < Min And Dist <> 0 Then
Min = Dist
End If
Wend
FindMinDist = Min
End Function

When I try using the function, it gives me back #VALUE! in the cell. I have my latitude in C4:C403 & longitude in D4:D403. Any ideas on how to make this work properly?

**Maddogwoof** · 07-22-2011, 12:29 PM

Figured it out! All good to go! Wrote it myself. Here it is if anyone is interested:

Please Login or Register  to view this content.

**shg** · 07-23-2011, 01:20 PM

Another way:

Please Login or Register  to view this content.

By passing the range containing the other lat-longs, Excel sees a dependency and will automatically recompute when the data changes. Example usage:

=FindMinDist(D2, E2, D4:E392)

The value returned is in radians, which means you can multiply by the earth radius in your preferred units (meters, miles, nautical miles, ...) to get linear (great circle) distance.

**Mahmut M72** · 02-05-2024, 03:26 PM

hey shg,
I used the second way and it works perfectly and thank you. How can I find which one is closest? For example, which bus stop?
orijin sheet1 (range c1:d1)

destination sheet2 (range c2: d15000) large data

column c (names) column m (lat) column n(long) column p (min dist)

**FDibbins** · 02-06-2024, 12:33 AM

Originally Posted by Mahmut M72

hey shg,
I used the second way and it works perfectly and thank you. How can I find which one is closest? For example, which bus stop?
orijin sheet1 (range c1:d1)

destination sheet2 (range c2: d15000) large data

column c (names) column m (lat) column n(long) column p (min dist)

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread