Searching a excel database for the 10 best results

**nick_94_94** · 01-19-2007, 02:42 PM

This is what I'm trying to do. I have a list of places, all with UTM coordinates. eg. 480323, 4205678. I would like to be able to enter a new UTM coordinate and hopefully excel can lookup the 10 closest places based on their UTM coordinates.

eg.
___Location____E-W_______N-S
___A place____488888____7288888
___B place____324586____4565899
___C place____125468____6954236

I would like to be able to enter a new UTM such as 264569 and 6800258, from that i would like excel to locate the top results from the database which are closest to the new location.

Can someone please help me out and let me know if this is possible to do, and if it is how i would do it. Thanks

nick

**mrice** · 01-19-2007, 03:45 PM

If you use the following user defined function

Function Distance(TestCoord1, TestCoord2, RefCoord1, RefCoord2)
Distance = ((TestCoord1 - RefCoord1) ^ 2 + (TestCoord2 - RefCoord2) ^ 2) ^ 0.5
End Function

You can then sort your data by Euclidian distance (calculated by Pythag.)

**nick_94_94** · 01-19-2007, 05:15 PM

Now i have a long list of locational along with the distance, I added a column beside the distance to rant them, but they are not in ascending order is there anyway to use some sort of a lookup function to find the top 10 results?

eg.
UTM Coordinate: whatever it is

Top 10 Nearest locations:

1______location
2______location
3______location
ect.

Because I have a long list of places with their utm, then their distance from the new coordinate, then their ranks.

I dont want to have to scroll through to find the top 10 closest locations. I want the results to appear at the top of the page where i type in the new UTM coordinate sinimlar to my example above. Is this possible?

nick

**mrice** · 01-19-2007, 06:52 PM

OK, try this

Sub Test()
Dim MyArray(10, 3)
Coord1 = Cells(1, 5)
Coord2 = Cells(1, 6)
For N = 2 To Cells(65536, 1).End(xlUp).Row
Distance = ((Cells(N, 1) - Coord1) ^ 2 + (Cells(N, 2) - Coord2) ^ 2) ^ 0.5
For M = 1 To 10
If Distance > MyArray(M, 3) Then
For P = 10 To M + 1 Step -1
MyArray(P, 1) = MyArray(P - 1, 1)
MyArray(P, 2) = MyArray(P - 1, 2)
MyArray(P, 3) = MyArray(P - 1, 3)
Next P
MyArray(M, 1) = Cells(N, 1)
MyArray(M, 2) = Cells(N, 2)
MyArray(M, 3) = Distance
Exit For
End If
Next M
Next N
For N = 1 To 10
Cells(N, 8) = MyArray(N, 1)
Cells(N, 9) = MyArray(N, 2)
Cells(N, 10) = MyArray(N, 3)
Next N
End Sub

The test coords are in E1 and F1 and the results appear in H1 to J10