Shortest distance between Lat/Longs with condition

[excelnovicetx]

Hi all,

This has been bothering me for the last few days. What I am looking to do is find the shortest distance in miles between two lat long points, (which I have figured out thanks to posts on this site). However, I also want to limit the result to just the nearest lat long point associated with a specific company in another column.

On my main tab:
Lat/Long 1 are in columns L and M
Company by Lat/Long is located in Column O

Recent Activity Tab:
Lat/Long 2 can be found in C in rows 3:650 and D in rows 3:650
Company by lat/long is located in Column E in rows 3:650

I then want to only find the shortest distance by Recent Activity Column E as it relates to Column O on my main tab

I have tried wrapping the working formula (below) in a match, an index, index match, indeex match match, and an If statement, etc and have not been able to make it work.

{=INDEX(ACOS(COS(RADIANS(90-L3))*COS(RADIANS(90-'Recent Activity'!$CN$3:$CN$650))+SIN(RADIANS(90-L3))*SIN(RADIANS(90-'Recent Activity'!$C$3:$C$650))*COS(RADIANS(M3-'Recent Activity'!$D$3:$D$650)))*3958.75,MATCH(SMALL((ABS('Recent Activity'!$C$3:$C$650-L3)^2+ABS('Recent Activity'!$D$3:$D$650-M3)^2)^(0.5),1),(ABS('Recent Activity'!$C$3:$C$650-L3)^2+ABS('Recent Activity'!$D$3:$D$650-M3)^2)^(0.5),0))}

The result should be the shortest number of miles between two lat longs only between those where the companies match.

Thanks for the help!

 

[DRSteele]

Welcome to the forum.

It's not really possible for most people to look at your post and propose a solution unless they have some data on which to conduct experiments. I've composed some sample data that uses NHL distances between arenas. After revieweing it, please tell use what you would like to do to that sample data to meet your needs.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Team	Name	City/Area	Arena	Coord_hms	Coor_dec	Latitude	Longitude
2	Anaheim	Anaheim Ducks	Anaheim, CA	Honda Center	3348'28?N 11752'36?W?	?33.80778N 117.87667W?	33.8078	-117.8767
3	Arizona	Arizona Coyotes	Glendale, AZ	Gila River Arena	3331'55?N 11215'40?W?	?33.53194N 112.26111W?	33.5319	-112.2611
4	Boston	Boston Bruins	Boston, MA	TD Garden	4221'58.69?N 713'44.02?W?	?42.3663028N 71.0622278W?	42.3663	-71.0622
5	Buffalo	Buffalo Sabres	Buffalo, NY	First Niagara Center	4252'30?N 7852'35?W?	?42.87500N 78.87639W?	42.8750	-78.8764
6	Calgary	Calgary Flames	Calgary, AB	Scotiabank Saddledome	5102'15?N 11403'07?W?	?51.03750N 114.05194W?	51.0375	-114.0519
7	Carolina	Carolina Hurricanes	Raleigh, NC	PNC Arena	3548'12?N 7843'19?W?	?35.80333N 78.72194W?	35.8033	-78.7219
8	Chicago	Chicago Blackhawks	Chicago, IL	United Center	4152'50?N 8740'27?W?	?41.88056N 87.67417W?	41.8806	-87.6742
9	Colorado	Colorado Avalanche	Denver, CO	Pepsi Center	3944'55?N 1050'27?W?	?39.74861N 105.00750W?	39.7486	-105.0075
10	Columbus	Columbus Blue Jackets	Columbus, OH	Nationwide Arena	3958'9.42?N 830'22.00?W?	?39.9692833N 83.0061111W?	39.9693	-83.0061
11	Dallas	Dallas Stars	Dallas, TX	American Airlines Center	3247'26?N 9648'37?W?	?32.79056N 96.81028W?	32.7906	-96.8103
12	Detroit	Detroit Red Wings	Detroit, MI	Joe Louis Arena	4219'31?N 833'5?W?	?42.32528N 83.05139W?	42.3253	-83.0514
13	Edmonton	Edmonton Oilers	Edmonton, AB	Rexall Place	5334'17?N 11327'22?W?	?53.57139N 113.45611W?	53.5714	-113.4561
14
53
54	dist (km)	Anaheim	Arizona	Boston	Buffalo	Calgary	Carolina	Chicago	Colorado	Columbus	Dallas	Detroit	Edmonton
55	Anaheim	0	521	4152	3513	1941	3558	2783	1321	3155	1958	3167	2225
56	Arizona	521	0	3705	3073	1952	3063	2343	946	2689	1439	2726	2230
57	Boston	4152	3705	0	642	3364	984	1369	2842	1034	2493	985	3333
58	Buffalo	3513	3073	642	0	2789	786	731	2201	472	1927	347	2788
59	Calgary	1941	1952	3364	2789	0	3273	2246	1438	2687	2467	2532	285
60	Carolina	3558	3063	984	786	3273	0	1027	2343	596	1692	816	3330
61	Chicago	2783	2343	1369	731	2246	1027	0	1475	446	1292	385	2305
62	Colorado	1321	946	2842	2201	1438	2343	1475	0	1873	1066	1858	1664
63	Columbus	3155	2689	1034	472	2687	596	446	1873	0	1469	262	2736
64	Dallas	1958	1439	2493	1927	2467	1692	1292	1066	1469	0	1607	2661
65	Detroit	3167	2726	985	347	2532	816	385	1858	262	1607	0	2559
66	Edmonton	2225	2230	3333	2788	285	3330	2305	1664	2736	2661	2559	0
Sheet2

Cell Formulas
Range	Formula
B55	=ACOS(COS(RADIANS(90-INDEX($G$2:$G$32,MATCH($A55,$A$2:$A$32,0)))) *COS(RADIANS(90-INDEX($G$2:$G$32,MATCH(B$54,$A$2:$A$32,0)))) +SIN(RADIANS(90-INDEX($G$2:$G$32,MATCH($A55,$A$2:$A$32,0)))) *SIN(RADIANS(90-INDEX($G$2:$G$32,MATCH(B$54,$A$2:$A$32,0)))) *COS(RADIANS(INDEX($H$2:$H$32,MATCH($A55,$A$2:$A$32,0))-INDEX($H$2:$H$32,MATCH(B$54,$A$2:$A$32,0)))))*6371

 

[excelnovicetx]

Thanks for the reply:

Main Tab


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]Column L (Lat)
[/TD]
[/TR]
[TR]
[TD="width: 70"]31.6639[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Column M (Long)

[TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.7729[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Column O (Company)

Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]31.7509[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.5517[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]31.7219[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.6876[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]31.7801[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.6366[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]32.4647[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.9937[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]32.4502[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.9938[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]32.3631[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.975[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]32.4324[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.6563[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="width: 70"]32.4205[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 82"]
<tbody>[TR]
[TD="width: 82"]-93.8403[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
</tbody>[/TABLE]

Recent Activity Tab:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]Column C (Lat)[/TD]
[TD]Column D (Long)[/TD]
[TD]Column E (Company)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.2222[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.5752[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.12232[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.5868[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.25154[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.4116[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.25085[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.4709[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.27449[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.6285[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Beta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.24886[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.8857[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.16435[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.8551[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]31.91699[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.5922[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.10503[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.3515[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Delta[/TD]
[/TR]
[TR]
[TD][TABLE="width: 70"]
<tbody>[TR]
[TD="class: xl95, width: 70"][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]32.21104[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]-93.9701[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Gamma[/TD]
[/TR]
</tbody>[/TABLE]


I can figure out the closest distance to each one, but want to limit to when only the closest one where the company is the same.

Does this help?

I added the underlined text to the formula this morning and it seems to help but also returns several "False" answers.

{=IF('Recent Activity'!$E$3:$E$651=$O2,INDEX(ACOS(COS(RADIANS(90-L3))*COS(RADIANS(90-'Recent Activity'!$CN$3:$CN$650))+SIN(RADIANS(90-L3))*SIN(RADIANS(90-'Recent Activity'!$C$3:$C$650))*COS(RADIANS(M3-'Recent Activity'!$D$3:$D$650)))*3958.75,MATCH(SMALL((ABS('Recent Activity'!$C$3:$C$650-L3)^2+ABS('Recent Activity'!$D$3:$D$650-M3)^2)^(0.5),1),(ABS('Recent Activity'!$C$3:$C$650-L3)^2+ABS('Recent Activity'!$D$3:$D$650-M3)^2)^(0.5),0))}