Calculate Closest Point from 2 Large Datasets with Coordinates (Lat / Lon)

[ialvaran]

I would like to calculate the closest distance between two large datasets (tables). Table 1 and Table 2 have location IDs and coordinates (LAT / LON). I would like to know which data point in Table 2 is closest to the data points in Table 1. The calculation should also include the closest (or shortest) distance in meters or feet.

Please find below sample tables (1 and 2) including a sample desired output.

Table 1

Location_ID_Table1	LATITUDE_Table1	LONGITUDE_Table1
Table1_0000001	32.7875116	-96.8042723
Table1_0000002	32.9506823	-97.0399645
Table1_0000003	32.9979846	-96.7823867
Table1_0000004	32.8288923	-97.4046827
Table1_0000005	32.8047396	-96.6839268
Table1_0000006	32.804776	-96.6839919
Table1_0000007	32.8048567	-96.6839509
Table1_0000008	32.9463226	-96.7004484
Table1_0000009	32.9554295	-96.7115144
Table1_0000010	33.1568095	-96.844646

<tbody>
</tbody>

Table 2

Location_ID_Table2	LATITUDE_Table2	LONGITUDE_Table2
Table2_0000001	32.7716152	-97.1064349
Table2_0000002	32.6356708	-96.9003413
Table2_0000003	32.6356984	-96.9002808
Table2_0000004	32.6357211	-96.9002707
Table2_0000005	32.6357226	-96.9003323
Table2_0000006	32.6357466	-96.9003532
Table2_0000007	32.6357724	-96.9002676
Table2_0000008	32.6357726	-96.9004123
Table2_0000009	32.6357788	-96.9002719
Table2_0000010	32.6076538	-97.0925508

<tbody>
</tbody>

Desired Output

Location_ID_Table1	LATITUDE_Table1	LONGITUDE_Table1	Distance from closest point from Table 2	Closest Location_ID_Table2
Table1_0000001	32.7875116	-96.8042723	0.01	Table2_0000014
Table1_0000002	32.9506823	-97.0399645	1.00	Table2_0000034
Table1_0000003	32.9979846	-96.7823867	2.15	Table2_0000678
Table1_0000004	32.8288923	-97.4046827	0.15	Table2_0000659
Table1_0000005	32.8047396	-96.6839268	5.00	Table2_0000012
Table1_0000006	32.804776	-96.6839919	0.35	Table2_0000032
Table1_0000007	32.8048567	-96.6839509	10.56	Table2_0000958
Table1_0000008	32.9463226	-96.7004484	3.25	Table2_0000026
Table1_0000009	32.9554295	-96.7115144	1.65	Table2_0001032
Table1_0000010	33.1568095	-96.844646	0.89	Table2_0000020

<tbody>
</tbody>

Thank you in advance for the help.

Jon

 

[DRSteele]

I believe what you need to do is create a formula that calculates all of the distances between all of the points. Review these threads and let us know whether or not they help.

https://www.mrexcel.com/forum/excel-questions/966498-create-territories.html

https://www.mrexcel.com/forum/excel...ongitude-coordinates-find-closest-office.html

credit is due here: BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

 

[ialvaran]

Mr. Steele,

Thank you for the quick reply. I have been site searching Mr. Excel via Google and was not able to find a viable solution.

Your second link that you provided below (https://www.mrexcel.com/forum/excel-...st-office.html) helped. That is exactly what I am looking for. Good thing you also provided the distance between points in one of your replies.

I appreciate your help to the community.

Thanks again.

Jon

I believe what you need to do is create a formula that calculates all of the distances between all of the points. Review these threads and let us know whether or not they help.

https://www.mrexcel.com/forum/excel-questions/966498-create-territories.html

https://www.mrexcel.com/forum/excel...ongitude-coordinates-find-closest-office.html

credit is due here: BlueMM: Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

 

[DRSteele]

You're welcome. I'm glad it helped.

Calculating the Great Circle Distance using those trig formulas allows us to create a grid of distances. Finding the shortest trips between them all is then a simple matter of INDEX and MATCH, I think.