Create Results File From comparing the distance of two list of Coordinates

[Wsdst2]

I have three Worksheets:

Matchback.xlsx
Sales.xlsx
DistanceCalc.xlsm

I want to Create a fourth sheet called MatchBack_Results.xlsx

I have included examples of all files. This looks like a loop within a loop, I'm just not sure how to execute it.

The MatchBack file and the Sales file will vary in length each time I have to perform this taks. The Line count is never the same, But I must compare Every set of coordinates in the Sales file to Each
set of coordinates in the MatchBack file.

I need to keep the closest Match in Meters, without duplicates and:
Place the entire row from the Sales file into the MatchBack_Results.xlsx file on the "SalesMatch" tab with the Distance placed at the end of the row, currently Cell AI or RC[35] AND
Place the entire row from the MatchBack file into the MatchBack_Results.xlsx file on the "OriginalMatch" tab with the Distance placed at the end of the row, currently Cell ET or RC[150]


If the Result is less than 3 meters in the MatchBack_Results.xlsx File on the SalesMatch tab place the word "EXACT" in the cell after the distance, Currently Cell AJ or RC[36]



The MatchBack File is the main file, I have converted all cooridnated to DMS. The Coordinates in this file will always be the Orgination Point with:

Orgination Latitude starts in Cell DA2 or RC[90]
Orgination Longitude starts in Cell DB2 or RC[91]

OLatDMS2-Txt being the Orgination Latitude, copy and pasted into the DistanceCalc Worksheet Cell B2 or RC[2]
OLongDMS2-Txt Being the Orgination Longitude, copy and pasted into the DistanceCalc Worksheet Cell C2 or RC[3]


The Next Step Would be to Open the Sales.xlsx I need to compare all the coordinates in this file and find the closest match to the coordinates in the MatchBack.xlsx file

Destination latitude Starts in Cell AD2 or RC[30]
Destination Longitude Starts in Cell AE2 or RC[31]

OLatDMS2-Txt being the Orgination Latitude, copy and pasted into the DistanceCalc Worksheet Cell B3 or RC[2]
OLongDMS2-Txt Being the ORgination Longitude, copy and pasted into the DistanceCalc Worksheet Cell C3 or RC[3]


The Distance in Meters is located in cell B5 on the DistanceCalc.xlsm Worksheet
I will discared all results over 528 feet or 160.934 Meters

Once the process is complete I would like the MatchBack_Results.xlsx sorted from Smallest to largest based on distance on each tab.

Any Assistance would be greatly appreciated.

I will attache MiniSheets. The Complete files are available. I would be happy to e-mail them

Thank you!

 

[Wsdst2]

MatchBack.xlsx:

MatchBack.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES
1	API_ID	FIRSTNAME	MIDDLE_INI	MIDDLENAME	LASTNAME	SURN_SUFF	SEX	BIRTHDATE	PARTY_CODE	PHONE_NUM	PHONE_CODE	PHONE_SRC	REGIS_DATE	ABSENTEE	AGE	DEAD	EMAIL	ETHNIC_INFER	ETHNICCODE	ETHNICCONF	ETHNICGRP	MOBILE_CODE	MOBILE_CONF	MOBILE_NUM	MRTLSTATUS	MVALIDFLAG	OCCDETAIL	PRFL_2NDAMEND	PRFL_ACTIVE_MIL	PRFL_ANML_RIGHTS	PRFL_CHOICELIFE	PRFL_CLINTON_SUPPORT	PRFL_EDUCATION	PRFL_ENVIRONMENT	PRFL_EVANGELICAL	PRFL_GUN_CONTROL	PRFL_HEALTHCARE	PRFL_HEALTHCARE_REFORM	PRFL_LABOR	PRFL_LGBT_SUPPORT	PRFL_MARIJUANA_REFORM	PRFL_MARRIAGE_EQUALITY	PRFL_MIL_SUPPORT	PRFL_OBAMA	PRFL_PERSUADABLE_VOTER	PRFL_POLITICAL_IDEOLOGY	PRFL_TAXES	PRFL_TEAPARTY	PRFL_VETERAN	RANDOM_NUM	STATUS	VOTER_ID	VP_GEN	VP_OTH	VP_PPP	VP_PRI	COUNTY_ST	ST_UP_HOUS	ST_LO_HOUS	CONG_DIST	AI_COUNTY_NAME	MUNICIPALITY	PREC_NAME	PREC_NO1	SCHL_DIST	TIMEZONE	WARD	ADDRESS	CITY	STATE	ZIP	ZIP4	MADDRESS	MCITY	MSTATE	MZIP	M_ZIP4	MCR_RT	ADD_TYPE	HOUSE_NUM	PRE_DIR	ST_NAME	POST_DIR	ST_SUFF	APT_NAME	APT_NUM	ODD_EV_ADD	LATITUDE	LONGITUDE	LatDMS	LongDMS	LatDMS-Txt	LongDMS-Txt	CENSUS_ST	CENSUS_TRK	CENSUS_BLK	COA_FLAG	COA_MOVE	COUNTYFIPS_USPS	MCOUNTYFIPS_USPS	RLAT	RLNG	LatDMS2	LongDMS2	OLatDMS2-Txt	OLongDMS2-Txt	CRA_INCCOD	DNC	DWELLTYPE	EDUCATION	ETHNIC_COD	FISHING	HH_NUMBER	HH_VETERAN	HOMEGARDEN	HOMEIMP	HOMEIMPDIY	HOMEMKTVAL	HOMEOWNER	HOMEOWNRNT	INCOMESTHH	LANGUAGE	LENGTH_RES	NETWORTH	NUMLINCRED	PARTY_MIX	PERSONS_HH	PRESENCHLD	RELIGINSP	RELIGION	SEWER	VALIDFLAG	VOTER_TRLR	WATER	WEALTH	YEARBUILT	FEC_01_02	FEC_03_04	FEC_05_06	FEC_07_08	FEC_09_10	FEC_11_12	FEC_13_14	FEC_15_16	FEC_93_94	FEC_95_96	FEC_97_98	FEC_99_00	PERM_INDIVIDUAL_ID
2	12345678911	DANIELLE	M		SEALS		F	19820314	R	999-999-9999			20041025		38		john.smith@yahoo.com	C	T3		W	2	MH	999-999-9999	M																				Y	C				226169	A	020425474-02	50	0	33.33	0	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 05	17800	WEST ALLEGHENY REGION 2	EST		845 Seabright Rd	Mc Donald	PA	15057	2121	845 Seabright Rd	Mc Donald	PA	15057	2121	R001	S	845		Seabright		Rd			O	40.409267	-80.256304	40° 24 ' 33.36 N	080° 15' 22.69 W	40° 24 ' 33.36 N	080° 15' 22.69 W	42	564000	1077			125	125	40.408198	-80.256826	40° 24 ' 29.51 N	080° 15' 24.57 W	40° 24 ' 29.51 N	080° 15' 24.57 W			S	B			2388174					J	V	O	O	E1	4	G		1	2	Y		P			1			1920													1602451230
3	12345678911	SARAH	M		DEMARIA		F	19810314	R	999-999-10000	2	V	20041009		39		john.smith@yahoo.com	C	T7		M			999-999-10000	M		H328			Y							Y								Y	C				226709	A	020425474-03	30	0	0	0	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 01	17800	WEST ALLEGHENY REGION 1	EST		107 Dupont Dr	Mc Donald	PA	15057	2189	107 Dupont Dr	Mc Donald	PA	15057	2189	R001	S	107		Dupont		Dr			O	40.394799	-80.206005	40° 23 ' 41.27 N	080° 12' 21.61 W	40° 23 ' 41.27 N	080° 12' 21.61 W	42	564000	2010			125	125	40.393769	-80.204307	40° 23 ' 37.56 N	080° 12' 15.50 W	40° 23 ' 37.56 N	080° 12' 15.50 W		Y	S	C			615268					N	V	O	R	E1	15	H	1	1	2	Y		C		D	1			2004													1602451230
4	12345678911	NAA	K		TETTEH		F	19810909	R	999-999-10001			20091011		39		john.smith@yahoo.com	B	MH	A	F	1	H	999-999-10001	M																					C				226215	A	020425474-04	30	0	0	0	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 01	17800	WEST ALLEGHENY REGION 1	EST		643 N Branch Rd	Mc Donald	PA	15057	2137	643 N Branch Rd	Mc Donald	PA	15057	2137	R001	S	643	N	Branch		Rd			O	40.403576	-80.237564	40° 24 ' 12.87 N	080° 14' 15.23 W	40° 24 ' 12.87 N	080° 14' 15.23 W	42	564000	2008			125	125	40.403444	-80.23797	40° 24 ' 12.39 N	080° 14' 16.69 W	40° 24 ' 12.39 N	080° 14' 16.69 W			S				2664311					K	V	O	Q	E1	5	G		1		Y					1			1960													1602451230
5	12345678911	BRYAN			HEFFERN		M	19880504	R	999-999-10002	2	N	20150406		32		john.smith@yahoo.com	C	T6		W	1	H	999-999-10002	M	Y																			Y	C	Y			226291	A	020425474-05	40	0	100	0	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 01	17800	WEST ALLEGHENY REGION 1	EST		1610 North Rd	Mc Donald	PA	15057	2158	1610 North Rd	Mc Donald	PA	15057	2158	R001	S	1610		North		Rd			E	40.405166	-80.22769	40° 24 ' 18.59 N	080° 13' 39.68 W	40° 24 ' 18.59 N	080° 13' 39.68 W	42	564000	2006			125	125	40.407468	-80.229208	40° 24 ' 26.88 N	080° 13' 45.14 W	40° 24 ' 26.88 N	080° 13' 45.14 W		Y	S	B			1110129					J	V	O	O	E1	5	G	2	1	4	Y		C		U	1		6	1984													1602451230
6	12345678911	JESSE	M		BUCK		M	19900711	R	999-999-10003	1	Z	20081014		30		john.smith@yahoo.com	C	T3		W			999-999-10003	S			Y																		C	Y			226294	A	020425474-06	10	0	0	0	3	37	44	17	ALLEGHENY	ROBINSON	ROBINSON-3RD	5003-	FORT CHERRY SCH DIST	EST		1601 North Rd	Mc Donald	PA	15057	2159	1601 North Rd	Mc Donald	PA	15057	2159	R001	S	1601		North		Rd			O	40.40828	-80.228448	40° 24 ' 29.80 N	080° 13' 42.41 W	40° 24 ' 29.80 N	080° 13' 42.41 W	42	564000	2005			125	125	40.409008	-80.229349	40° 24 ' 32.42 N	080° 13' 45.65 W	40° 24 ' 32.42 N	080° 13' 45.65 W	3	Y	S	B			320887					L	V	O	N	E1	15	F	1	1				P		Y	6			1992													1602451230
7	12345678911	LORI	A		HENARY		F	19820613	D	999-999-10004			20150306		38		john.smith@yahoo.com					2	L	999-999-10004	M		W533	Y		Y				Y							Y				Y	L	Y			226716	A	020425474-07	40	0	0	0	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 01	17800	WEST ALLEGHENY REGION 1	EST		113 Dupont Dr	Mc Donald	PA	15057	2189	113 Dupont Dr	Mc Donald	PA	15057	2189	R001	S	113		Dupont		Dr			O	40.394799	-80.206005	40° 23 ' 41.27 N	080° 12' 21.61 W	40° 23 ' 41.27 N	080° 12' 21.61 W	42	564000	2010			125	125	40.394126	-80.205419	40° 23 ' 38.85 N	080° 12' 19.50 W	40° 23 ' 38.85 N	080° 12' 19.50 W			S	B		Y	1120402			Y		N	V	O	R		5	H		2		Y					1			2004													1602451230
8	12345678911	MATTHEW	G		MANCINI		M	1991		999-999-10005	1	Y			30		john.smith@yahoo.com	C	T7		M	1		999-999-10005	S	Y																								226333		020425474-08					3	37	44	17	ALLEGHENY			N FAYETTE 00 01		EST		1907 North Rd	Mc Donald	PA	15057	2165	1907 North Rd	Mc Donald	PA	15057	2165	R001	S	1907		North		Rd			O	40.398281	-80.206553	40° 23 ' 53.81 N	080° 12' 23.59 W	40° 23 ' 53.81 N	080° 12' 23.59 W	42	564000	2011			125	125	40.397939	-80.206091	40° 23 ' 52.58 N	080° 12' 21.92 W	40° 23 ' 52.58 N	080° 12' 21.92 W			S	B			1632474					I	V	O	O	E1		G				Y		C		U				1959													1602451230
9	12345678911	REBECCA	J		DELANEY		F	1982		999-999-10006	1	H			39		john.smith@yahoo.com	C	T6		W	1	MH	999-999-10006	M	Y		Y		Y				Y																228952		020425474-09					3	37	46	17	ALLEGHENY			S FAYETTE 00 05		EST		1046 Granite Dr	Mc Donald	PA	15057	2518	1046 Granite Dr	Mc Donald	PA	15057	2518	R008	S	1046		Granite		Dr			E	40.378147	-80.198807	40° 22 ' 41.32 N	080° 11' 55.70 W	40° 22 ' 41.32 N	080° 11' 55.70 W	42	456003	3023			125	125	40.377886	-80.19907	40° 22 ' 40.38 N	080° 11' 56.65 W	40° 22 ' 40.38 N	080° 11' 56.65 W		Y	S	B		Y	607587					N	V	O	R	E1		I						C		Y				2014													1602451230
10	12345678911	ADAM			YAGULLI		M	19840415	D	999-999-10007			20080629		36		john.smith@yahoo.com	C	T7		M	1	H	999-999-10007	M	Y															Y				Y	L	Y			228962	A	020425474-10	10	0	33.33	0	3	37	46	17	ALLEGHENY	SOUTH FAYETTE TOWNSHIP	S FAYETTE 00 05	20400	SOUTH FAYETTE SCHOOL DISTRICT	EST		1056 Granite Dr	Mc Donald	PA	15057	2518	1056 Granite Dr	Mc Donald	PA	15057	2518	R008	S	1056		Granite		Dr			E	40.378147	-80.198807	40° 22 ' 41.32 N	080° 11' 55.70 W	40° 22 ' 41.32 N	080° 11' 55.70 W	42	456003	3023			125	125	40.376759	-80.198624	40° 22 ' 36.33 N	080° 11' 55.04 W	40° 22 ' 36.33 N	080° 11' 55.04 W			S				2947683					M	V	O	Q	E1	5	G		6	3			C			1		9	2015													1602451230
11	12345678911	AMANDA			LUSK		F	1985		999-999-10008	1	Z			36		john.smith@yahoo.com	C	T3		W			999-999-10008	S																									226740		020425474-11					3	37	44	17	ALLEGHENY			N FAYETTE 00 01		EST		104 Dupont Dr	Mc Donald	PA	15057	2190	104 Dupont Dr	Mc Donald	PA	15057	2190	R001	S	104		Dupont		Dr			E	40.393935	-80.206273	40° 23 ' 38.16 N	080° 12' 22.58 W	40° 23 ' 38.16 N	080° 12' 22.58 W	42	564000	2010			125	125	40.393332	-80.205009	40° 23 ' 35.99 N	080° 12' 18.03 W	40° 23 ' 35.99 N	080° 12' 18.03 W			S	B			1596581					N	V	O	R	E1	12	H				Y		P		D				2005													1602451230
12	12345678911	CARA			FRANCIS		F	19830106	D	999-999-10009	1	V	20121020		38		john.smith@yahoo.com	C	T6		W	1	ML	999-999-10009	M																Y					L				228969	A	020425474-12	37.5	0	0	0	3	37	46	17	ALLEGHENY	SOUTH FAYETTE TOWNSHIP	S FAYETTE 00 05	20400	SOUTH FAYETTE SCHOOL DISTRICT	EST		1066 Granite Dr	Mc Donald	PA	15057	2518	1066 Granite Dr	Mc Donald	PA	15057	2518	R008	S	1066		Granite		Dr			E	40.378147	-80.198807	40° 22 ' 41.32 N	080° 11' 55.70 W	40° 22 ' 41.32 N	080° 11' 55.70 W	42	456003	3023			125	125	40.375789	-80.198554	40° 22 ' 32.84 N	080° 11' 54.79 W	40° 22 ' 32.84 N	080° 11' 54.79 W			S	B			853239					M	V	O	O	E1	4	G		2		Y		C		Y	1			2016													1602451230
13	12345678911	JESSICA			SCHWEINBERG		F	19820324	D	999-999-10010			20041025		38		john.smith@yahoo.com	C	T5		W			999-999-10010	M																Y					L				229493	A	020425474-13	30	0	0	0	3	37	46	17	ALLEGHENY	SOUTH FAYETTE TOWNSHIP	S FAYETTE 00 05	20400	SOUTH FAYETTE SCHOOL DISTRICT	EST		1327 Sandstone Dr	Mc Donald	PA	15057	2563	1327 Sandstone Dr	Mc Donald	PA	15057	2563	R008	S	1327		Sandstone		Dr			O	40.376522	-80.199341	40° 22 ' 35.47 N	080° 11' 57.62 W	40° 22 ' 35.47 N	080° 11' 57.62 W	42	456003	3023			125	125	40.377074	-80.19938	40° 22 ' 37.46 N	080° 11' 57.76 W	40° 22 ' 37.46 N	080° 11' 57.76 W			S	B			2379470					L	V	O	S	E1	8	G		2		Y		P			1			2010													1602451230
14	12345678911	MELISSA			CAMPBELL		F	1985		999-999-10011					36		john.smith@yahoo.com	C	N6		W			999-999-10011	M																									229495		020425474-14					3	37	46	17	ALLEGHENY			S FAYETTE 00 05		EST		1329 Sandstone Dr	Mc Donald	PA	15057	2563	1329 Sandstone Dr	Mc Donald	PA	15057	2563	R008	S	1329		Sandstone		Dr			O	40.376522	-80.199341	40° 22 ' 35.47 N	080° 11' 57.62 W	40° 22 ' 35.47 N	080° 11' 57.62 W	42	456003	3023			125	125	40.376851	-80.199316	40° 22 ' 36.66 N	080° 11' 57.53 W	40° 22 ' 36.66 N	080° 11' 57.53 W			S	B			366772					K	V	O	Q	E1	8	G						P						2012													1602451230
15	12345678911	CHRISTOPHER	J		SMITH		M	19821223	D	999-999-10012		O	20010906		38		john.smith@yahoo.com	C	T3		W			999-999-10012	M					Y				Y							Y					L	Y			225172	A	020425474-15	20	0	33.33	10	3	37	44	17	ALLEGHENY	NORTH FAYETTE TOWNSHIP	N FAYETTE 00 05	17800	WEST ALLEGHENY REGION 2	EST		117 Anchor Ct	Mc Donald	PA	15057	1500	117 Anchor Ct	Mc Donald	PA	15057	1500	R001	S	117		Anchor		Ct			O	40.40518	-80.253875	40° 24 ' 18.64 N	080° 15' 13.94 W	40° 24 ' 18.64 N	080° 15' 13.94 W	42	564000	1079			125	125	40.40518	-80.253559	40° 24 ' 18.64 N	080° 15' 12.81 W	40° 24 ' 18.64 N	080° 15' 12.81 W			S	B			2490290			Y		M	V	O	Q	E1	7	G	1	2				P			1		2	2013													1602451230
16	12345678911	COURTNEY			SULLIVAN		F	19810701	R	999-999-10013	2	Z	19991001		39		john.smith@yahoo.com	C	T6		W	1	MH	999-999-10013	M	Y		Y															Y		Y	C	Y			228971	A	020425474-16	50	10	33.33	0	3	37	46	17	ALLEGHENY	SOUTH FAYETTE TOWNSHIP	S FAYETTE 00 05	20400	SOUTH FAYETTE SCHOOL DISTRICT	EST		1001 Granite Dr	Mc Donald	PA	15057	2519	1001 Granite Dr	Mc Donald	PA	15057	2519	R008	S	1001		Granite		Dr			O	40.37902	-80.198223	40° 22 ' 44.47 N	080° 11' 53.60 W	40° 22 ' 44.47 N	080° 11' 53.60 W	42	456003	3023			125	125	40.381744	-80.201358	40° 22 ' 54.27 N	080° 12' 04.88 W	40° 22 ' 54.27 N	080° 12' 04.88 W		Y	S	B			2617075					N	V	O	S	E1	11	H	1	5				C		Y	1			2006													1602451230
17	12345678911	MARIA	A		BUDASH		F	19810725		999-999-10014	2	Y			39		john.smith@yahoo.com							999-999-10014	S																		Y							226381		020425474-17					3	37	44	17	ALLEGHENY			N FAYETTE 00 05		EST		101 Commodore Dr	Mc Donald	PA	15057	2177	101 Commodore Dr	Mc Donald	PA	15057	2177	R001	S	101		Commodore		Dr			O	40.406572	-80.258746	40° 24 ' 23.65 N	080° 15' 31.48 W	40° 24 ' 23.65 N	080° 15' 31.48 W	42	564000	1079			125	125	40.407713	-80.25787	40° 24 ' 27.76 N	080° 15' 28.33 W	40° 24 ' 27.76 N	080° 15' 28.33 W		Y	S	B			322950					M	V	O	O		5	H				Y				D				2015													1602451230
18	12345678911	ANTHONY			BATTISTONE		M	19860514	D	999-999-10015	1	Z	20081025		34		john.smith@yahoo.com	C	T3		W			999-999-10015																	Y					L	Y			230156	A	020425474-18	10	0	0	0	3	37	46	17	ALLEGHENY	SOUTH FAYETTE TOWNSHIP	S FAYETTE 00 03	20400	SOUTH FAYETTE SCHOOL DISTRICT	EST		211 Stuart Cir	Mc Donald	PA	15057	2616	211 Stuart Cir	Mc Donald	PA	15057	2616	R008	S	211		Stuart		Cir			O	40.37702	-80.166798	40° 22 ' 37.27 N	080° 10' 00.47 W	40° 22 ' 37.27 N	080° 10' 00.47 W	42	456001	1023			3	3	40.377031	-80.166346	40° 22 ' 37.31 N	080° 09' 58.84 W	40° 22 ' 37.31 N	080° 09' 58.84 W		Y	S				138636					K	V	O	Q	E1				2	3			P		Y	3		6	1987													1602451230
19	12345678911	JASON	N		MORRIS		M	1982		999-999-10016	1	Y			39		john.smith@yahoo.com	C	T3		W			999-999-10016	S																									228078		020425474-19					3	37	44	17	ALLEGHENY			N FAYETTE 00 01		EST		4803 Main St	Mc Donald	PA	15057	2357	4803 Main St	Mc Donald	PA	15057	2357	R005	S	4803		Main		St			O	40.38562	-80.211154	40° 23 ' 08.23 N	080° 12' 40.15 W	40° 23 ' 08.23 N	080° 12' 40.15 W	42	564000	2033			3	3	40.385844	-80.210548	40° 23 ' 09.03 N	080° 12' 37.97 W	40° 23 ' 09.03 N	080° 12' 37.97 W			S				1868118					G	V	O	M	E1	15	G						P		D				1924													1602451230
PL-Robinson-Millennial-Test

Cell Formulas
Range	Formula
CL2:CL19,CY2:CY19	CL2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(CJ2),0),"00"),"° ",TEXT(ROUNDDOWN(ABS((CJ2-ROUNDDOWN(CJ2,0))*60),0),"00")," ' ",TEXT(TRUNC((ABS((CJ2-ROUNDDOWN(CJ2,0))*60)-ROUNDDOWN(ABS((CJ2-ROUNDDOWN(CJ2,0))*60),0))*60,2),"00.00"),"",IF(CJ2<0," S"," N"))
CM2:CM19,CZ2:CZ19	CM2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(CK2),0),"000"),"° ",TEXT(ROUNDDOWN(ABS((CK2-ROUNDDOWN(CK2,0))*60),0),"00"),"' ",TEXT(TRUNC((ABS((CK2-ROUNDDOWN(CK2,0))*60)-ROUNDDOWN(ABS((CK2-ROUNDDOWN(CK2,0))*60),0))*60,2),"00.00"),"",IF(CK2<0," W"," E"))

 

[Wsdst2]

Sales.xlsx:

Sales.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Sold Date	Type	Txn Type	ID	Model	Sex	Birth Date	Days On Hand	Arrvial Date	Orig Sale Price	Txn Price	Txn Discount	Txn Ext Price	Registry	Microchip Number	Employee	Contact Number	Customer First Name	Customer Last Name	Customer Address 1	Customer Address 2	Customer City	Customer State	Customer Zip Code	Concatanated Address	Latitude1	Longitude1	LatDMS	LongDMS	DLatDMS-Txt	DLongDMS	Customer Email	Location	Invoice Number
2	11/02/2020	Puppy	Sale	6105	ZZZZZZ	Male	07/21/2020	39	09/24/2020		6,924.07	0.00	6,924.07	All	991001003714757	Miyah	999-999-9999	Dionte Vickers	Amari Muhammad	7441 Penfeild Ct		pittsburgh	PA	15208	7441 Penfeild CtpittsburghPA15208	40.4485782	-79.8968471	40° 26 ' 54.88 N	079° 53' 48.64 W	40° 26 ' 54.88 N	079° 53' 48.64 W	abc@abc123.com	Bora Bora	123456
3	11/03/2020	Puppy	Sale	6201	ZZZZZZ	Female	08/17/2020	14	10/20/2020		6,924.07	0.00	6,924.07	All	992000000403281	Miyah	999-999-10000	James	Crawford	4205 Stratford Dr		Irwin	PA	15642	4205 Stratford DrIrwinPA15642	40.2940622	-79.672729	40° 17 ' 38.62 N	079° 40' 21.82 W	40° 17 ' 38.62 N	079° 40' 21.82 W	abc@abc123.com	Bora Bora	123457
4	11/03/2020	Puppy	Sale	6238	ZZZZZZ	Female	08/21/2020	7	10/27/2020		6,924.07	0.00	6,924.07	All	992000000403853	Miyah	999-999-10001	James	Lauteri	1012 Bomar ct		Mars	PA	16046	1012 Bomar ctMarsPA16046	40.69484	-79.991217	40° 41 ' 41.42 N	079° 59' 28.38 W	40° 41 ' 41.42 N	079° 59' 28.38 W	abc@abc123.com	Bora Bora	123458
5	11/04/2020	Puppy	Sale	6251	ZZZZZZ	Female	08/29/2020	6	10/29/2020		6,924.07	0.00	6,924.07	All	991001003718450	Bri	999-999-10002	Cayley	Coglio	68 Boulder Drive		Pittsburgh	PA	15239	68 Boulder DrivePittsburghPA15239	40.4876733	-79.7811614	40° 29 ' 15.62 N	079° 46' 52.18 W	40° 29 ' 15.62 N	079° 46' 52.18 W	abc@abc123.com	Bora Bora	123459
6	11/05/2020	Puppy	Sale	6161	ZZZZZZ	Male	08/01/2020	28	10/08/2020		6,924.07	0.00	6,924.07	All	991001003714158	Bri	999-999-10003	Karen	Payne	448 Billy Drive		Pittsburgh	PA	15235	448 Billy DrivePittsburghPA15235	40.4943916	-79.7997717	40° 29 ' 39.80 N	079° 47' 59.17 W	40° 29 ' 39.80 N	079° 47' 59.17 W	abc@abc123.com	Bora Bora	123460
7	11/07/2020	Puppy	Sale	6189	ZZZZZZ	Female	08/10/2020	24	10/14/2020		6,924.07	0.00	6,924.07	All	992000000395453	Bri	999-999-10004	Shannon	Devona	206 E coffee st		uniontown	PA	15401	206 E coffee stuniontownPA15401	39.9079169	-79.7173107	39° 54 ' 28.50 N	079° 43' 02.31 W	39° 54 ' 28.50 N	079° 43' 02.31 W	abc@abc123.com	Bora Bora	123461
8	11/07/2020	Puppy	Sale	6266	ZZZZZZ	Female	08/17/2020	5	11/02/2020		6,924.07	0.00	6,924.07	All	992000000388845	Miyah	999-999-10005	John	Smith	1405 Maplehurst dr		somerset	PA	15501	1405 Maplehurst drsomersetPA15501	39.9927554	-79.091535	39° 59 ' 33.91 N	079° 05' 29.52 W	39° 59 ' 33.91 N	079° 05' 29.52 W	abc@abc123.com	Bora Bora	123462
9	11/07/2020	Puppy	Sale	6218	ZZZZZZ	Male	08/23/2020	16	10/22/2020		6,924.07	0.00	6,924.07	All	991001003879697	Bri	999-999-10006	John	Smith	4596 Compressor Station Rd		Bruceton Mills	WV	26525	4596 Compressor Station RdBruceton MillsWV26525	39.713515	-79.6784	39° 42 ' 48.65 N	079° 40' 42.23 W	39° 42 ' 48.65 N	079° 40' 42.23 W	abc@abc123.com	Bora Bora	123463
10	11/08/2020	Puppy	Sale	6159	ZZZZZZ	Male	08/10/2020	31	10/08/2020		6,924.07	0.00	6,924.07	All	991001003714200	Bri	999-999-10007	John	Smith	116 Julz Drive		Johnstown	PA	15904	116 Julz DriveJohnstownPA15904	40.3267407	-78.9219698	40° 19 ' 36.26 N	078° 55' 19.09 W	40° 19 ' 36.26 N	078° 55' 19.09 W	abc@abc123.com	Bora Bora	123464
11	11/09/2020	Puppy	Sale	6156	ZZZZZZ	Male	08/09/2020	32	10/08/2020		6,924.07	0.00	6,924.07	All	991001003714542	Bri	999-999-10008	John	Smith	1065 Blue Ridge Road		Clarington	PA	15828	1065 Blue Ridge RoadClaringtonPA15828	41.3618187	-79.1128268	41° 21 ' 42.54 N	079° 06' 46.17 W	41° 21 ' 42.54 N	079° 06' 46.17 W	abc@abc123.com	Bora Bora	123465
12	11/09/2020	Puppy	Sale	6253	ZZZZZZ	Female	08/23/2020	11	10/29/2020		6,924.07	0.00	6,924.07	All	991001003880392	Bri	999-999-10009	John	Smith	1807 Main Street		Arona	PA	15617	1807 Main StreetAronaPA15617	40.2632236	-79.6494869	40° 15 ' 47.60 N	079° 38' 58.15 W	40° 15 ' 47.60 N	079° 38' 58.15 W	abc@abc123.com	Bora Bora	123466
13	11/11/2020	Puppy	Layaway	6252	ZZZZZZ	Female	08/26/2020	13	10/29/2020		6,924.07	0.00	6,924.07	All	933000320400828	Bri	999-999-10010	John	Smith	1221 Ventana Drive		Coraopolis	PA	15108	1221 Ventana DriveCoraopolisPA15108	40.4674203	-80.1497809	40° 28 ' 02.71 N	080° 08' 59.21 W	40° 28 ' 02.71 N	080° 08' 59.21 W	abc@abc123.com	Bora Bora	123467
14	11/12/2020	Service	Sale	6254	ZZZZZZ	Male	08/28/2020	14	10/29/2020		6,924.07	0.00	6,924.07	All	991001003880381	Miyah	999-999-10011	John	Smith	8880 Westwood Rd		Pittsburgh	PA	15235	8880 Westwood RdPittsburghPA15235	40.461555	-79.872326	40° 27 ' 41.59 N	079° 52' 20.37 W	40° 27 ' 41.59 N	079° 52' 20.37 W	abc@abc123.com	Bora Bora	123468
15	11/12/2020	Service	Sale	6220	ZZZZZZ	Female	08/24/2020	21	10/22/2020		6,924.07	0.00	6,924.07	All	991001003879714	Rozele	999-999-10012	John	Smith	35 Brush St		Manor	PA	15665	35 Brush StManorPA15665	40.3310964	-79.6685277	40° 19 ' 51.94 N	079° 40' 06.69 W	40° 19 ' 51.94 N	079° 40' 06.69 W	abc@abc123.com	Bora Bora	123469
16	11/13/2020	Service	Layaway	6296	ZZZZZZ	Male	09/09/2020	3	11/10/2020		6,924.07	0.00	6,924.07	All	991001003866521	Miyah	999-999-10013	John	Smith	1275 Belton Road		Ellwood City	PA	16117	1275 Belton RoadEllwood CityPA16117	40.829941	-80.3065249	40° 49 ' 47.78 N	080° 18' 23.48 W	40° 49 ' 47.78 N	080° 18' 23.48 W	abc@abc123.com	Bora Bora	123470
17	11/14/2020	Service	Sale	6299	ZZZZZZ	Female	09/11/2020	2	11/12/2020		6,924.07	0.00	6,924.07	All	991001003880754	Miyah	999-999-10014	John	Smith	135 John Dr		Canonsburg	PA	15317	135 John DrCanonsburgPA15317	40.275002	-80.1847815	40° 16 ' 30.00 N	080° 11' 05.21 W	40° 16 ' 30.00 N	080° 11' 05.21 W	abc@abc123.com	Bora Bora	123471
18	11/14/2020	Service	Sale	6203	ZZZZZZ	Male	08/15/2020	25	10/20/2020		6,924.07	0.00	6,924.07	All	992000000395505	Bri	999-999-10015	John	Smith	2512 Pointsettia Drive		White Oak	PA	15131	2512 Pointsettia DriveWhite OakPA15131	40.3346679	-79.8141126	40° 20 ' 04.80 N	079° 48' 50.80 W	40° 20 ' 04.80 N	079° 48' 50.80 W	abc@abc123.com	Bora Bora	123472
19	11/14/2020	Service	Sale	6300	ZZZZZZ	Male	09/10/2020	2	11/12/2020		6,924.07	0.00	6,924.07	All	991001003880352	Bri	999-999-10016	John	Smith	700 Graham Ave		Windber	PA	15963	700 Graham AveWindberPA15963	40.240462	-78.8380375	40° 14 ' 25.66 N	078° 50' 16.93 W	40° 14 ' 25.66 N	078° 50' 16.93 W	abc@abc123.com	Bora Bora	123473
20	11/14/2020	Service	Sale	6236	ZZZZZZ	Male	08/23/2020	18	10/27/2020		6,924.07	0.00	6,924.07	All	992000000403835	Bri	999-999-10017	John	Smith	71 W Hallam Ave		Washington	PA	15301	71 W Hallam AveWashingtonPA15301	40.1775908	-80.2494912	40° 10 ' 39.32 N	080° 14' 58.16 W	40° 10 ' 39.32 N	080° 14' 58.16 W	abc@abc123.com	Bora Bora	123474
21	11/15/2020	Service	Sale	6276	ZZZZZZ	Male	09/03/2020	13	11/02/2020		6,924.07	0.00	6,924.07	All	991001003868740	Miyah	999-999-10018	John	Smith	366 Harrison Ave		Leechburg	PA	15656	366 Harrison AveLeechburgPA15656	40.6288173	-79.600789	40° 37 ' 43.74 N	079° 36' 02.84 W	40° 37 ' 43.74 N	079° 36' 02.84 W	abc@abc123.com	Bora Bora	123475
22	11/16/2020	Service	Sale	6301	ZZZZZZ	Male	09/01/2020	4	11/12/2020		6,924.07	0.00	6,924.07	All	991001003880380	Miyah	999-999-10019	John	Smith	248 Fingal St		Pittsburgh	PA	15211	248 Fingal StPittsburghPA15211	40.4392436	-80.0275335	40° 26 ' 21.27 N	080° 01' 39.12 W	40° 26 ' 21.27 N	080° 01' 39.12 W	abc@abc123.com	Bora Bora	123476
23	11/16/2020	Service	Sale	6240	ZZZZZZ	Male	08/21/2020	20	10/27/2020		6,924.07	0.00	6,924.07	All	992000000403846	Miyah	999-999-10020	John	Smith	115 Mountain View Hts		Greensburg	PA	15601	115 Mountain View HtsGreensburgPA15601	40.2821756	-79.4717728	40° 16 ' 55.83 N	079° 28' 18.38 W	40° 16 ' 55.83 N	079° 28' 18.38 W	abc@abc123.com	Bora Bora	123477
24	11/18/2020	Service	Sale	6318	ZZZZZZ	Male	09/08/2020	1	11/17/2020		6,924.07	0.00	6,924.07	All	992000000452451	Bri	999-999-10021	John	Smith	580 Audubon Avenue		Pittsburgh	PA	15228	580 Audubon AvenuePittsburghPA15228	40.37744	-80.0287127	40° 22 ' 38.78 N	080° 01' 43.36 W	40° 22 ' 38.78 N	080° 01' 43.36 W	abc@abc123.com	Bora Bora	123478
25	11/18/2020	Service	Sale	6255	ZZZZZZ	Male	08/26/2020	20	10/29/2020		6,924.07	0.00	6,924.07	All	991001003879702	Manager Elliot	999-999-10022	John	Smith	235 Foxwood Rd		Wampum	PA	16157	235 Foxwood RdWampumPA16157	40.8188342	-80.3401751	40° 49 ' 07.80 N	080° 20' 24.63 W	40° 49 ' 07.80 N	080° 20' 24.63 W	abc@abc123.com	Bora Bora	123479
Details

Cell Formulas
Range	Formula
AB2:AB25	AB2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(Z2),0),"00"),"° ",TEXT(ROUNDDOWN(ABS((Z2-ROUNDDOWN(Z2,0))*60),0),"00")," ' ",TEXT(TRUNC((ABS((Z2-ROUNDDOWN(Z2,0))*60)-ROUNDDOWN(ABS((Z2-ROUNDDOWN(Z2,0))*60),0))*60,2),"00.00"),"",IF(Z2<0," S"," N"))
AC2:AC25	AC2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(AA2),0),"000"),"° ",TEXT(ROUNDDOWN(ABS((AA2-ROUNDDOWN(AA2,0))*60),0),"00"),"' ",TEXT(TRUNC((ABS((AA2-ROUNDDOWN(AA2,0))*60)-ROUNDDOWN(ABS((AA2-ROUNDDOWN(AA2,0))*60),0))*60,2),"00.00"),"",IF(AA2<0," W"," E"))
Y2:Y25	Y2	=CONCATENATE(T2,V2,W2,X2)

 

[Wsdst2]

DistanceCalc.xlsm:

DistanceCalc.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Latitude	Longitude			Reference Test Values and Expected Results
2	Origination Point	40° 24 ' 33.36 N	080° 15' 22.69 W			Latitude	Longitude
3	Destination Point	40° 26 ' 54.88 N	079° 53' 48.64 W			37~ 57' 3.7203" S	144° 25' 29.5244" E
4						37° 39' 10.1561" S	143~ 55' 35.3839" E
5	Distance in Meters:	30,813.3610	Forward Azimuth ATAN2:	81.712592		Correct Results (distance in meters)
6
7						54972.271	Bearing via ATAN2:		307.0951
8	Conversions to other measurements:
9	To Statute Miles	19.147
10	To Nautical Miles	16.638
11	To Kilometers	30.813
12	To Feet	101,093.704
13	To Inches	1,213,124.448
14
15	To Furlong	6,198,674.099
16	To Cubit	14,087.869
17	To Palm	2,311.002
18	To Finger	570.047
19	To Step	22,801.887
20	To Pace	45,603.774
21	To Perch	9,120.755
22	To League	14.006
23
24
25
26	Note on Forward Azimuth:
27		Forward Azimuth is the initial heading along the Great Circle path.
28		Since the GC path is an arc across a sphere, it requires adjustments during the transit.
29		For example, flying from Baghdad to Osaka, the Forward Azimuth is 60, but at the
30		end of the trip the heading will be 120.
31		This bearing is relative to True North and would no doubt require adjustments
32		if using a magnetic compass.
33
DistanceByCoordinates

Cell Formulas
Range	Formula
B5	B5	=distvincenty(signit(B2),signit(C2),signit(B3),signit(C3))
D5	D5	=MOD(DEGREES(ATAN2(COS(signit(B2)*PI()/180)*SIN(signit(B3)*PI()/180)-SIN(signit(B2)*PI()/180)*COS(signit(B3)*PI()/180)*COS(signit(C3)*PI()/180-signit(C2)*PI()/180),SIN(signit(C3)*PI()/180-signit(C2)*PI()/180)*COS(signit(B2)*PI()/180)))+360,360)
B9	B9	=((B$5*39.3700787)/12)/5280
B10	B10	=((B$5*39.3700787)/12)/6076.11549
B11	B11	=B5/1000
B12	B12	=(B$5*39.3700787)/12
B13	B13	=B$5*39.3700787
B15	B15	=B5/0.00497096
B16	B16	=B5*0.4572
B17	B17	=B5*0.075
B18	B18	=B5*0.0185
B19	B19	=B5*0.74
B20	B20	=B5*1.48
B21	B21	=B5*0.296
B22	B22	=B5/2200

 

[Wsdst2]

MatchBack_Results.xlsx:

MatchBack_Results.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Sold Date	Type	Txn Type	ID	Model	Sex	Birth Date	Days On Hand	Arrvial Date	Orig Sale Price	Txn Price	Txn Discount	Txn Ext Price	Registry	Microchip Number	Employee	Contact Number	Customer First Name	Customer Last Name	Customer Address 1	Customer Address 2	Customer City	Customer State	Customer Zip Code	Concatanated Address	Latitude1	Longitude1	LatDMS	LongDMS	DLatDMS-Txt	DLongDMS	Customer Email	Location	Invoice Number	Distance	Type
2	11/02/2020	Service	Sale	6105	ZZZZZZ	Male	07/21/2020	39	09/24/2020		6,924.07	0.00	6,924.07	All	991001003714757	Miyah	999-999-9999	Dionte Vickers	Amari Muhammad	7441 Penfeild Ct		pittsburgh	PA	15208	7441 Penfeild CtpittsburghPA15208	40.448578	-79.896847	40° 26 ' 54.88 N	079° 53' 48.64 W	40° 26 ' 54.88 N	079° 53' 48.64 W	abc@abc123.com	Bora Bora	123456
3	11/03/2020	Service	Sale	6201	ZZZZZZ	Female	08/17/2020	14	10/20/2020		6,924.07	0.00	6,924.07	All	992000000403281	Miyah	999-999-10000	James	Crawford	4205 Stratford Dr		Irwin	PA	15642	4205 Stratford DrIrwinPA15642	40.294062	-79.672729	40° 17 ' 38.62 N	079° 40' 21.82 W	40° 17 ' 38.62 N	079° 40' 21.82 W	abc@abc123.com	Bora Bora	123457
4	11/03/2020	Service	Sale	6238	ZZZZZZ	Female	08/21/2020	7	10/27/2020		6,924.07	0.00	6,924.07	All	992000000403853	Miyah	999-999-10001	James	Lauteri	1012 Bomar ct		Mars	PA	16046	1012 Bomar ctMarsPA16046	40.69484	-79.991217	40° 41 ' 41.42 N	079° 59' 28.38 W	40° 41 ' 41.42 N	079° 59' 28.38 W	abc@abc123.com	Bora Bora	123458
5	11/04/2020	Service	Sale	6251	ZZZZZZ	Female	08/29/2020	6	10/29/2020		6,924.07	0.00	6,924.07	All	991001003718450	Bri	999-999-10002	Cayley	Coglio	68 Boulder Drive		Pittsburgh	PA	15239	68 Boulder DrivePittsburghPA15239	40.487673	-79.781161	40° 29 ' 15.62 N	079° 46' 52.18 W	40° 29 ' 15.62 N	079° 46' 52.18 W	abc@abc123.com	Bora Bora	123459
SalesMatch

Cell Formulas
Range	Formula
AB2:AB5	AB2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(Z2),0),"00"),"° ",TEXT(ROUNDDOWN(ABS((Z2-ROUNDDOWN(Z2,0))*60),0),"00")," ' ",TEXT(TRUNC((ABS((Z2-ROUNDDOWN(Z2,0))*60)-ROUNDDOWN(ABS((Z2-ROUNDDOWN(Z2,0))*60),0))*60,2),"00.00"),"",IF(Z2<0," S"," N"))
AC2:AC5	AC2	=CONCATENATE(TEXT(ROUNDDOWN(ABS(AA2),0),"000"),"° ",TEXT(ROUNDDOWN(ABS((AA2-ROUNDDOWN(AA2,0))*60),0),"00"),"' ",TEXT(TRUNC((ABS((AA2-ROUNDDOWN(AA2,0))*60)-ROUNDDOWN(ABS((AA2-ROUNDDOWN(AA2,0))*60),0))*60,2),"00.00"),"",IF(AA2<0," W"," E"))
Y2:Y5	Y2	=CONCATENATE(T2,V2,W2,X2)