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
LocationLatitudeLongitudeClosest City 1Closest City 2Closest City 3
San Fran
37.781383

<tbody>
</tbody>
-122.460533

<tbody>
</tbody>

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

LocationLatitudeLongitude
Denver
40.395369

<tbody>
</tbody>
-104.756684
Chicago
41.92975

<tbody>
</tbody>
-87.7467
Cleveland
41.418133

<tbody>
</tbody>
-81.787063
Asheville
35.53621

<tbody>
</tbody>
-82.60152
Houston
29.6795

<tbody>
</tbody>
-95.538717

<tbody>
</tbody>


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

Thanks!!!
 
Last edited:

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,386
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.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Location</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Closest City 3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">San Fran</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">37.78138</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-122.460533</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FAFAFA;;"></td><td style="text-align: right;border-top: 1px solid black;background-color: #FAFAFA;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Location</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Latitude2</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Longitude2</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Denver</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">40.39537</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-104.756684</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">2.613986</td><td style="text-align: right;border-top: 1px solid black;;">17.703849</td><td style="text-align: right;;">20.317835</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Houston</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">29.6795</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-95.538717</td><td style="text-align: right;border-left: 1px solid black;;">-8.101883</td><td style="text-align: right;;">26.921816</td><td style="text-align: right;;">35.023699</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Chicago</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">41.92975</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-87.7467</td><td style="text-align: right;border-left: 1px solid black;;">4.148367</td><td style="text-align: right;;">34.713833</td><td style="text-align: right;;">38.8622</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Asheville</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">35.53621</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-82.60152</td><td style="text-align: right;border-left: 1px solid black;;">-2.245173</td><td style="text-align: right;;">39.859013</td><td style="text-align: right;;">42.104186</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">Cleveland</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;background-color: #FAFAFA;;">41.41813</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;color: #333333;background-color: #FAFAFA;;">-81.787063</td><td style="text-align: right;border-left: 1px solid black;;">3.63675</td><td style="text-align: right;;">40.67347</td><td style="text-align: right;;">44.31022</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D6</th><td style="text-align:left">=B6-$B$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">E6</th><td style="text-align:left">=C6-$C$2</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">F6</th><td style="text-align:left">=ABS(<font color="Blue">D6</font>)+ABS(<font color="Blue">E6</font>)</td></tr></tbody></table></td></tr></table><br />
 

aallaman

New Member
Joined
Dec 4, 2009
Messages
44
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,148
Messages
5,657,109
Members
418,355
Latest member
michaelirl

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
Top