Calculate 5 closest locations to any given location

[Veem]

Hi Everyone,

I have done a search on the forum and found some threads but no solution.

Below is the data I have in the second tab of my worksheet.

On the first tab I want to be able to enter any site code and I want the result of of the 5 nearest sites returned ?

Any ideas ?


SITE CODE Latitude Longitude
M338 -22.605430 39.040540
M298 -32.923340 19.422780
M361 -22.105120 29.121320
M330 -32.571310 29.811520
M002 -22.857130 39.027990
M357 -24.746350 29.190740
M384 -32.023860 39.909320
M236 -24.745033 29.203142
M090 -24.769882 29.089913

Thanks,

[Pete_UK]

Please confirm that you are still using Excel 2003 - that is a very old version of Excel. If you have updated to a later version, then please adjust this on your profile by clicking on User CP in the top menu.

Pete

[MrShorty]

I don't know how others would do it. I'm also not sure exactly how you want to interact with the spreadsheet. Here's one way I would perform this calculation (should be readily backwards compatible to 2003, if you are indeed still using Excel 2003):

0) For now, I will ignore what happens on sheet1. Some of this can be moved to sheet1, but, for purposes of development, I find it easier to work on the same sheet as the source data. I will also assume the source data are in column A, B, and C and the upper left is A1.
1) In a convenient cell (maybe F1) enter the desired site code. In G1 and H1, a lookup function will return latitude and longitude for the site code entered in F1 G1 might be =VLOOKUP($F1,$A$2:$C$10,2,FALSE). Similar in H1.
2) In D1, enter "distance". In D2, enter the desired distance formula between this site and the site chosen in F1. I'm not sure if you are assuming a Euclidian approximation of the distance or if you are using a more robust geometry for your distance formula. Whatever your distance formula is, enter it into D2, pay attention to relative and absolute references, then copy/paste/fill into D2:D10.
3) Now we can use the SMALL() function and some lookup functions to identify the 5 or 6 smallest distances and the associated site code.
4) Enter the numbers 1 to 6 in F2:F7.
5) Enter a SMALL() function into G2:G7 =SMALL($D$2:$D$10,G2)
6) A MATCH() function can find the row number corresponding to the distance returned in (5) MATCH(G2,$D$2:$D$10,0). An INDEX() function can return the corresponding site code =INDEX($A$2:$A$10,MATCH(...))

Once you are comfortable that it all works correctly, then you can "move" the stuff in columns F:H into sheet1 and interact with the spreadsheet on sheet1.