Expert level: Proximity matching for latitude/longitude

aallaman

New Member
Joined
Dec 4, 2009
Messages
44
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!!!
 
Last edited:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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
ABCDEF
1LocationLatitudeLongitudeClosest City 1Closest City 2Closest City 3
2San Fran37.78138-122.460533
3
4
5LocationLatitudeLongitudeLatitude2Longitude2
6Denver40.39537-104.7566842.61398617.70384920.317835
7Houston29.6795-95.538717-8.10188326.92181635.023699
8Chicago41.92975-87.74674.14836734.71383338.8622
9Asheville35.53621-82.60152-2.24517339.85901342.104186
10Cleveland41.41813-81.7870633.6367540.6734744.31022
Sheet1
Cell Formulas
RangeFormula
D6=B6-$B$2
E6=C6-$C$2
F6=ABS(D6)+ABS(E6)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top