Expert level: Proximity matching for latitude/longitude

[aallaman]

Hi Excel Geniuses

I've got a doozy of a question. I'm trying to take a center point (lat/long) and then use that value to reference a table to find the three closest matches by lat/long proximity.

Here is a simplified example:

The first spreadsheet has the centering location value and we want to populate the three closest cities from referencing the second table
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[TD]Closest City 1[/TD]
[TD]Closest City 2[/TD]
[TD]Closest City 3[/TD]
[/TR]
[TR]
[TD]San Fran[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]37.781383[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-122.460533[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The second table is thousands of rows long and has one lat/long per row

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Latitude[/TD]
[TD]Longitude[/TD]
[/TR]
[TR]
[TD]Denver[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]40.395369[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-104.756684[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]41.92975[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-87.7467[/TD]
[/TR]
[TR]
[TD]Cleveland[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]41.418133[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-81.787063[/TD]
[/TR]
[TR]
[TD]Asheville[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]35.53621[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-82.60152[/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="width: 53, align: right"]29.6795[/TD]
[TD="width: 53, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]-95.538717[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any idea how to use either a formula or VBA to make these proximity matches??

Thanks!!!

 

[Comfy]

I have no idea but gonna through out an idea anyway.

Would it be safe to say that if we sum the difference between each location Lat and each location Long and rank them by the result, that the lowest value would be the closest?

So I've not added a lookup yet but just sorted using the above logic.

Is this even remotely correct? Sorry not from the States so couldn't really check.

Excel 2010
	A	B	C	D	E	F
1	Location	Latitude	Longitude	Closest City 1	Closest City 2	Closest City 3
2	San Fran	37.78138	-122.460533
3
4
5	Location	Latitude	Longitude	Latitude2	Longitude2
6	Denver	40.39537	-104.756684	2.613986	17.703849	20.317835
7	Houston	29.6795	-95.538717	-8.101883	26.921816	35.023699
8	Chicago	41.92975	-87.7467	4.148367	34.713833	38.8622
9	Asheville	35.53621	-82.60152	-2.245173	39.859013	42.104186
10	Cleveland	41.41813	-81.787063	3.63675	40.67347	44.31022
Sheet1

Cell Formulas
Range	Formula
D6	=B6-$B$2
E6	=C6-$C$2
F6	=ABS(D6)+ABS(E6)

 

[AngelJ]

Here's my try: https://docs.google.com/spreadsheet/ccc?key=0AtCuYslrmRbBdGJSOEIyaExkX0NqR2ZjU2lVR1BveFE&usp=sharing

Does this look right?

 

[shg]

There's a workbook that locates the nearest city to a meteorological station at find the closest match to given latitude / longitude from data stored in 2 columns [SOLVED]

 

[aallaman]

Wow you all are awesome!

@Comfy, looks like that might work

@AngelJ, that is a work of art!

@shg, thanks for the link I'll dig into that script.