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

Wsdst2

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

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.
MatchBack.xlsx:

MatchBack.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBEBFBGBHBIBJBKBLBMBNBOBPBQBRBSBTBUBVBWBXBYBZCACBCCCDCECFCGCHCICJCKCLCMCNCOCPCQCRCSCTCUCVCWCXCYCZDADBDCDDDEDFDGDHDIDJDKDLDMDNDODPDQDRDSDTDUDVDWDXDYDZEAEBECEDEEEFEGEHEIEJEKELEMENEOEPEQERES
1API_IDFIRSTNAMEMIDDLE_INIMIDDLENAMELASTNAMESURN_SUFFSEXBIRTHDATEPARTY_CODEPHONE_NUMPHONE_CODEPHONE_SRCREGIS_DATEABSENTEEAGEDEADEMAILETHNIC_INFERETHNICCODEETHNICCONFETHNICGRPMOBILE_CODEMOBILE_CONFMOBILE_NUMMRTLSTATUSMVALIDFLAGOCCDETAILPRFL_2NDAMENDPRFL_ACTIVE_MILPRFL_ANML_RIGHTSPRFL_CHOICELIFEPRFL_CLINTON_SUPPORTPRFL_EDUCATIONPRFL_ENVIRONMENTPRFL_EVANGELICALPRFL_GUN_CONTROLPRFL_HEALTHCAREPRFL_HEALTHCARE_REFORMPRFL_LABORPRFL_LGBT_SUPPORTPRFL_MARIJUANA_REFORMPRFL_MARRIAGE_EQUALITYPRFL_MIL_SUPPORTPRFL_OBAMAPRFL_PERSUADABLE_VOTERPRFL_POLITICAL_IDEOLOGYPRFL_TAXESPRFL_TEAPARTYPRFL_VETERANRANDOM_NUMSTATUSVOTER_IDVP_GENVP_OTHVP_PPPVP_PRICOUNTY_STST_UP_HOUSST_LO_HOUSCONG_DISTAI_COUNTY_NAMEMUNICIPALITYPREC_NAMEPREC_NO1SCHL_DISTTIMEZONEWARDADDRESSCITYSTATEZIPZIP4MADDRESSMCITYMSTATEMZIPM_ZIP4MCR_RTADD_TYPEHOUSE_NUMPRE_DIRST_NAMEPOST_DIRST_SUFFAPT_NAMEAPT_NUMODD_EV_ADDLATITUDELONGITUDELatDMSLongDMSLatDMS-TxtLongDMS-TxtCENSUS_STCENSUS_TRKCENSUS_BLKCOA_FLAGCOA_MOVECOUNTYFIPS_USPSMCOUNTYFIPS_USPSRLATRLNGLatDMS2LongDMS2OLatDMS2-TxtOLongDMS2-TxtCRA_INCCODDNCDWELLTYPEEDUCATIONETHNIC_CODFISHINGHH_NUMBERHH_VETERANHOMEGARDENHOMEIMPHOMEIMPDIYHOMEMKTVALHOMEOWNERHOMEOWNRNTINCOMESTHHLANGUAGELENGTH_RESNETWORTHNUMLINCREDPARTY_MIXPERSONS_HHPRESENCHLDRELIGINSPRELIGIONSEWERVALIDFLAGVOTER_TRLRWATERWEALTHYEARBUILTFEC_01_02FEC_03_04FEC_05_06FEC_07_08FEC_09_10FEC_11_12FEC_13_14FEC_15_16FEC_93_94FEC_95_96FEC_97_98FEC_99_00PERM_INDIVIDUAL_ID
212345678911DANIELLEMSEALSF19820314R999-999-9999 20041025 38 john.smith@yahoo.comCT3 W2MH999-999-9999M YC 226169A020425474-0250033.3303374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0517800WEST ALLEGHENY REGION 2EST 845 Seabright RdMc DonaldPA150572121845 Seabright RdMc DonaldPA150572121R001S845SeabrightRdO40.409267-80.25630440° 24 ' 33.36 N080° 15' 22.69 W40° 24 ' 33.36 N080° 15' 22.69 W42564000107712512540.408198-80.25682640° 24 ' 29.51 N080° 15' 24.57 W40° 24 ' 29.51 N080° 15' 24.57 W SB 2388174 JVOOE14G 12Y P 1 1920 1602451230
312345678911SARAHMDEMARIAF19810314R999-999-100002V20041009 39 john.smith@yahoo.comCT7 M 999-999-10000MH328 Y Y YC 226709A020425474-03300003374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0117800WEST ALLEGHENY REGION 1EST 107 Dupont DrMc DonaldPA150572189107 Dupont DrMc DonaldPA150572189R001S107DupontDrO40.394799-80.20600540° 23 ' 41.27 N080° 12' 21.61 W40° 23 ' 41.27 N080° 12' 21.61 W42564000201012512540.393769-80.20430740° 23 ' 37.56 N080° 12' 15.50 W40° 23 ' 37.56 N080° 12' 15.50 W YSC 615268 NVORE115H112Y C D1 2004 1602451230
412345678911NAAKTETTEHF19810909R999-999-10001 20091011 39 john.smith@yahoo.comBMHAF1H999-999-10001M C 226215A020425474-04300003374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0117800WEST ALLEGHENY REGION 1EST 643 N Branch RdMc DonaldPA150572137643 N Branch RdMc DonaldPA150572137R001S643NBranchRdO40.403576-80.23756440° 24 ' 12.87 N080° 14' 15.23 W40° 24 ' 12.87 N080° 14' 15.23 W42564000200812512540.403444-80.2379740° 24 ' 12.39 N080° 14' 16.69 W40° 24 ' 12.39 N080° 14' 16.69 W S 2664311 KVOQE15G 1Y 1 1960 1602451230
512345678911BRYANHEFFERNM19880504R999-999-100022N20150406 32 john.smith@yahoo.comCT6 W1H999-999-10002MY YCY 226291A020425474-0540010003374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0117800WEST ALLEGHENY REGION 1EST 1610 North RdMc DonaldPA1505721581610 North RdMc DonaldPA150572158R001S1610NorthRdE40.405166-80.2276940° 24 ' 18.59 N080° 13' 39.68 W40° 24 ' 18.59 N080° 13' 39.68 W42564000200612512540.407468-80.22920840° 24 ' 26.88 N080° 13' 45.14 W40° 24 ' 26.88 N080° 13' 45.14 W YSB 1110129 JVOOE15G214Y C U1 61984 1602451230
612345678911JESSEMBUCKM19900711R999-999-100031Z20081014 30 john.smith@yahoo.comCT3 W 999-999-10003S Y CY 226294A020425474-06100003374417ALLEGHENYROBINSONROBINSON-3RD5003-FORT CHERRY SCH DISTEST 1601 North RdMc DonaldPA1505721591601 North RdMc DonaldPA150572159R001S1601NorthRdO40.40828-80.22844840° 24 ' 29.80 N080° 13' 42.41 W40° 24 ' 29.80 N080° 13' 42.41 W42564000200512512540.409008-80.22934940° 24 ' 32.42 N080° 13' 45.65 W40° 24 ' 32.42 N080° 13' 45.65 W3YSB 320887 LVONE115F11 P Y6 1992 1602451230
712345678911LORIAHENARYF19820613D999-999-10004 20150306 38 john.smith@yahoo.com 2L999-999-10004MW533Y YY Y YLY 226716A020425474-07400003374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0117800WEST ALLEGHENY REGION 1EST 113 Dupont DrMc DonaldPA150572189113 Dupont DrMc DonaldPA150572189R001S113DupontDrO40.394799-80.20600540° 23 ' 41.27 N080° 12' 21.61 W40° 23 ' 41.27 N080° 12' 21.61 W42564000201012512540.394126-80.20541940° 23 ' 38.85 N080° 12' 19.50 W40° 23 ' 38.85 N080° 12' 19.50 W SB Y1120402 Y NVOR 5H 2Y 1 2004 1602451230
812345678911MATTHEWGMANCINIM1991 999-999-100051Y 30 john.smith@yahoo.comCT7 M1999-999-10005SY 226333 020425474-083374417ALLEGHENYN FAYETTE 00 01EST1907 North RdMc DonaldPA1505721651907 North RdMc DonaldPA150572165R001S1907NorthRdO40.398281-80.20655340° 23 ' 53.81 N080° 12' 23.59 W40° 23 ' 53.81 N080° 12' 23.59 W42564000201112512540.397939-80.20609140° 23 ' 52.58 N080° 12' 21.92 W40° 23 ' 52.58 N080° 12' 21.92 W SB 1632474 IVOOE1 G Y C U 1959 1602451230
912345678911REBECCAJDELANEYF1982 999-999-100061H 39 john.smith@yahoo.comCT6 W1MH999-999-10006MY YYY228952 020425474-093374617ALLEGHENYS FAYETTE 00 05EST1046 Granite DrMc DonaldPA1505725181046 Granite DrMc DonaldPA150572518R008S1046GraniteDrE40.378147-80.19880740° 22 ' 41.32 N080° 11' 55.70 W40° 22 ' 41.32 N080° 11' 55.70 W42456003302312512540.377886-80.1990740° 22 ' 40.38 N080° 11' 56.65 W40° 22 ' 40.38 N080° 11' 56.65 W YSB Y607587 NVORE1 I C Y 2014 1602451230
1012345678911ADAMYAGULLIM19840415D999-999-10007 20080629 36 john.smith@yahoo.comCT7 M1H999-999-10007MY Y YLY 228962A020425474-1010033.3303374617ALLEGHENYSOUTH FAYETTE TOWNSHIPS FAYETTE 00 0520400SOUTH FAYETTE SCHOOL DISTRICTEST 1056 Granite DrMc DonaldPA1505725181056 Granite DrMc DonaldPA150572518R008S1056GraniteDrE40.378147-80.19880740° 22 ' 41.32 N080° 11' 55.70 W40° 22 ' 41.32 N080° 11' 55.70 W42456003302312512540.376759-80.19862440° 22 ' 36.33 N080° 11' 55.04 W40° 22 ' 36.33 N080° 11' 55.04 W S 2947683 MVOQE15G 63 C 1 92015 1602451230
1112345678911AMANDALUSKF1985 999-999-100081Z 36 john.smith@yahoo.comCT3 W 999-999-10008S 226740 020425474-113374417ALLEGHENYN FAYETTE 00 01EST104 Dupont DrMc DonaldPA150572190104 Dupont DrMc DonaldPA150572190R001S104DupontDrE40.393935-80.20627340° 23 ' 38.16 N080° 12' 22.58 W40° 23 ' 38.16 N080° 12' 22.58 W42564000201012512540.393332-80.20500940° 23 ' 35.99 N080° 12' 18.03 W40° 23 ' 35.99 N080° 12' 18.03 W SB 1596581 NVORE112H Y P D 2005 1602451230
1212345678911CARAFRANCISF19830106D999-999-100091V20121020 38 john.smith@yahoo.comCT6 W1ML999-999-10009M Y L 228969A020425474-1237.50003374617ALLEGHENYSOUTH FAYETTE TOWNSHIPS FAYETTE 00 0520400SOUTH FAYETTE SCHOOL DISTRICTEST 1066 Granite DrMc DonaldPA1505725181066 Granite DrMc DonaldPA150572518R008S1066GraniteDrE40.378147-80.19880740° 22 ' 41.32 N080° 11' 55.70 W40° 22 ' 41.32 N080° 11' 55.70 W42456003302312512540.375789-80.19855440° 22 ' 32.84 N080° 11' 54.79 W40° 22 ' 32.84 N080° 11' 54.79 W SB 853239 MVOOE14G 2Y C Y1 2016 1602451230
1312345678911JESSICASCHWEINBERGF19820324D999-999-10010 20041025 38 john.smith@yahoo.comCT5 W 999-999-10010M Y L 229493A020425474-13300003374617ALLEGHENYSOUTH FAYETTE TOWNSHIPS FAYETTE 00 0520400SOUTH FAYETTE SCHOOL DISTRICTEST 1327 Sandstone DrMc DonaldPA1505725631327 Sandstone DrMc DonaldPA150572563R008S1327SandstoneDrO40.376522-80.19934140° 22 ' 35.47 N080° 11' 57.62 W40° 22 ' 35.47 N080° 11' 57.62 W42456003302312512540.377074-80.1993840° 22 ' 37.46 N080° 11' 57.76 W40° 22 ' 37.46 N080° 11' 57.76 W SB 2379470 LVOSE18G 2Y P 1 2010 1602451230
1412345678911MELISSACAMPBELLF1985 999-999-10011 36 john.smith@yahoo.comCN6 W 999-999-10011M 229495 020425474-143374617ALLEGHENYS FAYETTE 00 05EST1329 Sandstone DrMc DonaldPA1505725631329 Sandstone DrMc DonaldPA150572563R008S1329SandstoneDrO40.376522-80.19934140° 22 ' 35.47 N080° 11' 57.62 W40° 22 ' 35.47 N080° 11' 57.62 W42456003302312512540.376851-80.19931640° 22 ' 36.66 N080° 11' 57.53 W40° 22 ' 36.66 N080° 11' 57.53 W SB 366772 KVOQE18G P 2012 1602451230
1512345678911CHRISTOPHERJSMITHM19821223D999-999-10012O20010906 38 john.smith@yahoo.comCT3 W 999-999-10012M YY Y LY 225172A020425474-1520033.33103374417ALLEGHENYNORTH FAYETTE TOWNSHIPN FAYETTE 00 0517800WEST ALLEGHENY REGION 2EST 117 Anchor CtMc DonaldPA150571500117 Anchor CtMc DonaldPA150571500R001S117AnchorCtO40.40518-80.25387540° 24 ' 18.64 N080° 15' 13.94 W40° 24 ' 18.64 N080° 15' 13.94 W42564000107912512540.40518-80.25355940° 24 ' 18.64 N080° 15' 12.81 W40° 24 ' 18.64 N080° 15' 12.81 W SB 2490290 Y MVOQE17G12 P 1 22013 1602451230
1612345678911COURTNEYSULLIVANF19810701R999-999-100132Z19991001 39 john.smith@yahoo.comCT6 W1MH999-999-10013MY Y YYCY 228971A020425474-16501033.3303374617ALLEGHENYSOUTH FAYETTE TOWNSHIPS FAYETTE 00 0520400SOUTH FAYETTE SCHOOL DISTRICTEST 1001 Granite DrMc DonaldPA1505725191001 Granite DrMc DonaldPA150572519R008S1001GraniteDrO40.37902-80.19822340° 22 ' 44.47 N080° 11' 53.60 W40° 22 ' 44.47 N080° 11' 53.60 W42456003302312512540.381744-80.20135840° 22 ' 54.27 N080° 12' 04.88 W40° 22 ' 54.27 N080° 12' 04.88 W YSB 2617075 NVOSE111H15 C Y1 2006 1602451230
1712345678911MARIAABUDASHF19810725 999-999-100142Y 39 john.smith@yahoo.com 999-999-10014S Y226381 020425474-173374417ALLEGHENYN FAYETTE 00 05EST101 Commodore DrMc DonaldPA150572177101 Commodore DrMc DonaldPA150572177R001S101CommodoreDrO40.406572-80.25874640° 24 ' 23.65 N080° 15' 31.48 W40° 24 ' 23.65 N080° 15' 31.48 W42564000107912512540.407713-80.2578740° 24 ' 27.76 N080° 15' 28.33 W40° 24 ' 27.76 N080° 15' 28.33 W YSB 322950 MVOO 5H Y D 2015 1602451230
1812345678911ANTHONYBATTISTONEM19860514D999-999-100151Z20081025 34 john.smith@yahoo.comCT3 W 999-999-10015 Y LY 230156A020425474-18100003374617ALLEGHENYSOUTH FAYETTE TOWNSHIPS FAYETTE 00 0320400SOUTH FAYETTE SCHOOL DISTRICTEST211 Stuart CirMc DonaldPA150572616211 Stuart CirMc DonaldPA150572616R008S211StuartCirO40.37702-80.16679840° 22 ' 37.27 N080° 10' 00.47 W40° 22 ' 37.27 N080° 10' 00.47 W4245600110233340.377031-80.16634640° 22 ' 37.31 N080° 09' 58.84 W40° 22 ' 37.31 N080° 09' 58.84 W YS 138636 KVOQE1 23 P Y3 61987 1602451230
1912345678911JASONNMORRISM1982 999-999-100161Y 39 john.smith@yahoo.comCT3 W 999-999-10016S 228078 020425474-193374417ALLEGHENYN FAYETTE 00 01EST4803 Main StMc DonaldPA1505723574803 Main StMc DonaldPA150572357R005S4803MainStO40.38562-80.21115440° 23 ' 08.23 N080° 12' 40.15 W40° 23 ' 08.23 N080° 12' 40.15 W4256400020333340.385844-80.21054840° 23 ' 09.03 N080° 12' 37.97 W40° 23 ' 09.03 N080° 12' 37.97 W S 1868118 GVOME115G P D 1924 1602451230
PL-Robinson-Millennial-Test
Cell Formulas
RangeFormula
CL2:CL19,CY2:CY19CL2=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:CZ19CM2=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"))
 
Upvote 0
Sales.xlsx:

Sales.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Sold DateTypeTxn TypeIDModelSexBirth DateDays On HandArrvial DateOrig Sale PriceTxn PriceTxn DiscountTxn Ext PriceRegistryMicrochip NumberEmployeeContact NumberCustomer First NameCustomer Last NameCustomer Address 1Customer Address 2Customer CityCustomer StateCustomer Zip CodeConcatanated AddressLatitude1Longitude1LatDMSLongDMSDLatDMS-TxtDLongDMSCustomer EmailLocationInvoice Number
211/02/2020PuppySale6105ZZZZZZMale07/21/20203909/24/20206,924.070.006,924.07All991001003714757Miyah999-999-9999Dionte VickersAmari Muhammad7441 Penfeild CtpittsburghPA152087441 Penfeild CtpittsburghPA1520840.4485782-79.896847140° 26 ' 54.88 N079° 53' 48.64 W40° 26 ' 54.88 N079° 53' 48.64 Wabc@abc123.comBora Bora123456
311/03/2020PuppySale6201ZZZZZZFemale08/17/20201410/20/20206,924.070.006,924.07All992000000403281Miyah999-999-10000JamesCrawford4205 Stratford DrIrwinPA156424205 Stratford DrIrwinPA1564240.2940622-79.67272940° 17 ' 38.62 N079° 40' 21.82 W40° 17 ' 38.62 N079° 40' 21.82 Wabc@abc123.comBora Bora123457
411/03/2020PuppySale6238ZZZZZZFemale08/21/2020710/27/20206,924.070.006,924.07All992000000403853Miyah999-999-10001JamesLauteri1012 Bomar ctMarsPA160461012 Bomar ctMarsPA1604640.69484-79.99121740° 41 ' 41.42 N079° 59' 28.38 W40° 41 ' 41.42 N079° 59' 28.38 Wabc@abc123.comBora Bora123458
511/04/2020PuppySale6251ZZZZZZFemale08/29/2020610/29/20206,924.070.006,924.07All991001003718450Bri999-999-10002CayleyCoglio68 Boulder DrivePittsburghPA1523968 Boulder DrivePittsburghPA1523940.4876733-79.781161440° 29 ' 15.62 N079° 46' 52.18 W40° 29 ' 15.62 N079° 46' 52.18 Wabc@abc123.comBora Bora123459
611/05/2020PuppySale6161ZZZZZZMale08/01/20202810/08/20206,924.070.006,924.07All991001003714158Bri999-999-10003KarenPayne448 Billy DrivePittsburghPA15235448 Billy DrivePittsburghPA1523540.4943916-79.799771740° 29 ' 39.80 N079° 47' 59.17 W40° 29 ' 39.80 N079° 47' 59.17 Wabc@abc123.comBora Bora123460
711/07/2020PuppySale6189ZZZZZZFemale08/10/20202410/14/20206,924.070.006,924.07All992000000395453Bri999-999-10004ShannonDevona206 E coffee stuniontownPA15401206 E coffee stuniontownPA1540139.9079169-79.717310739° 54 ' 28.50 N079° 43' 02.31 W39° 54 ' 28.50 N079° 43' 02.31 Wabc@abc123.comBora Bora123461
811/07/2020PuppySale6266ZZZZZZFemale08/17/2020511/02/20206,924.070.006,924.07All992000000388845Miyah999-999-10005JohnSmith1405 Maplehurst drsomersetPA155011405 Maplehurst drsomersetPA1550139.9927554-79.09153539° 59 ' 33.91 N079° 05' 29.52 W39° 59 ' 33.91 N079° 05' 29.52 Wabc@abc123.comBora Bora123462
911/07/2020PuppySale6218ZZZZZZMale08/23/20201610/22/20206,924.070.006,924.07All991001003879697Bri999-999-10006JohnSmith4596 Compressor Station RdBruceton MillsWV265254596 Compressor Station RdBruceton MillsWV2652539.713515-79.678439° 42 ' 48.65 N079° 40' 42.23 W39° 42 ' 48.65 N079° 40' 42.23 Wabc@abc123.comBora Bora123463
1011/08/2020PuppySale6159ZZZZZZMale08/10/20203110/08/20206,924.070.006,924.07All991001003714200Bri999-999-10007JohnSmith116 Julz DriveJohnstownPA15904116 Julz DriveJohnstownPA1590440.3267407-78.921969840° 19 ' 36.26 N078° 55' 19.09 W40° 19 ' 36.26 N078° 55' 19.09 Wabc@abc123.comBora Bora123464
1111/09/2020PuppySale6156ZZZZZZMale08/09/20203210/08/20206,924.070.006,924.07All991001003714542Bri999-999-10008JohnSmith1065 Blue Ridge RoadClaringtonPA158281065 Blue Ridge RoadClaringtonPA1582841.3618187-79.112826841° 21 ' 42.54 N079° 06' 46.17 W41° 21 ' 42.54 N079° 06' 46.17 Wabc@abc123.comBora Bora123465
1211/09/2020PuppySale6253ZZZZZZFemale08/23/20201110/29/20206,924.070.006,924.07All991001003880392Bri999-999-10009JohnSmith1807 Main StreetAronaPA156171807 Main StreetAronaPA1561740.2632236-79.649486940° 15 ' 47.60 N079° 38' 58.15 W40° 15 ' 47.60 N079° 38' 58.15 Wabc@abc123.comBora Bora123466
1311/11/2020PuppyLayaway6252ZZZZZZFemale08/26/20201310/29/20206,924.070.006,924.07All933000320400828Bri999-999-10010JohnSmith1221 Ventana DriveCoraopolisPA151081221 Ventana DriveCoraopolisPA1510840.4674203-80.149780940° 28 ' 02.71 N080° 08' 59.21 W40° 28 ' 02.71 N080° 08' 59.21 Wabc@abc123.comBora Bora123467
1411/12/2020ServiceSale6254ZZZZZZMale08/28/20201410/29/20206,924.070.006,924.07All991001003880381Miyah999-999-10011JohnSmith8880 Westwood RdPittsburghPA152358880 Westwood RdPittsburghPA1523540.461555-79.87232640° 27 ' 41.59 N079° 52' 20.37 W40° 27 ' 41.59 N079° 52' 20.37 Wabc@abc123.comBora Bora123468
1511/12/2020ServiceSale6220ZZZZZZFemale08/24/20202110/22/20206,924.070.006,924.07All991001003879714Rozele999-999-10012JohnSmith35 Brush StManorPA1566535 Brush StManorPA1566540.3310964-79.668527740° 19 ' 51.94 N079° 40' 06.69 W40° 19 ' 51.94 N079° 40' 06.69 Wabc@abc123.comBora Bora123469
1611/13/2020ServiceLayaway6296ZZZZZZMale09/09/2020311/10/20206,924.070.006,924.07All991001003866521Miyah999-999-10013JohnSmith1275 Belton RoadEllwood CityPA161171275 Belton RoadEllwood CityPA1611740.829941-80.306524940° 49 ' 47.78 N080° 18' 23.48 W40° 49 ' 47.78 N080° 18' 23.48 Wabc@abc123.comBora Bora123470
1711/14/2020ServiceSale6299ZZZZZZFemale09/11/2020211/12/20206,924.070.006,924.07All991001003880754Miyah999-999-10014JohnSmith135 John DrCanonsburgPA15317135 John DrCanonsburgPA1531740.275002-80.184781540° 16 ' 30.00 N080° 11' 05.21 W40° 16 ' 30.00 N080° 11' 05.21 Wabc@abc123.comBora Bora123471
1811/14/2020ServiceSale6203ZZZZZZMale08/15/20202510/20/20206,924.070.006,924.07All992000000395505Bri999-999-10015JohnSmith2512 Pointsettia DriveWhite OakPA151312512 Pointsettia DriveWhite OakPA1513140.3346679-79.814112640° 20 ' 04.80 N079° 48' 50.80 W40° 20 ' 04.80 N079° 48' 50.80 Wabc@abc123.comBora Bora123472
1911/14/2020ServiceSale6300ZZZZZZMale09/10/2020211/12/20206,924.070.006,924.07All991001003880352Bri999-999-10016JohnSmith700 Graham AveWindberPA15963700 Graham AveWindberPA1596340.240462-78.838037540° 14 ' 25.66 N078° 50' 16.93 W40° 14 ' 25.66 N078° 50' 16.93 Wabc@abc123.comBora Bora123473
2011/14/2020ServiceSale6236ZZZZZZMale08/23/20201810/27/20206,924.070.006,924.07All992000000403835Bri999-999-10017JohnSmith71 W Hallam AveWashingtonPA1530171 W Hallam AveWashingtonPA1530140.1775908-80.249491240° 10 ' 39.32 N080° 14' 58.16 W40° 10 ' 39.32 N080° 14' 58.16 Wabc@abc123.comBora Bora123474
2111/15/2020ServiceSale6276ZZZZZZMale09/03/20201311/02/20206,924.070.006,924.07All991001003868740Miyah999-999-10018JohnSmith366 Harrison AveLeechburgPA15656366 Harrison AveLeechburgPA1565640.6288173-79.60078940° 37 ' 43.74 N079° 36' 02.84 W40° 37 ' 43.74 N079° 36' 02.84 Wabc@abc123.comBora Bora123475
2211/16/2020ServiceSale6301ZZZZZZMale09/01/2020411/12/20206,924.070.006,924.07All991001003880380Miyah999-999-10019JohnSmith248 Fingal StPittsburghPA15211248 Fingal StPittsburghPA1521140.4392436-80.027533540° 26 ' 21.27 N080° 01' 39.12 W40° 26 ' 21.27 N080° 01' 39.12 Wabc@abc123.comBora Bora123476
2311/16/2020ServiceSale6240ZZZZZZMale08/21/20202010/27/20206,924.070.006,924.07All992000000403846Miyah999-999-10020JohnSmith115 Mountain View HtsGreensburgPA15601115 Mountain View HtsGreensburgPA1560140.2821756-79.471772840° 16 ' 55.83 N079° 28' 18.38 W40° 16 ' 55.83 N079° 28' 18.38 Wabc@abc123.comBora Bora123477
2411/18/2020ServiceSale6318ZZZZZZMale09/08/2020111/17/20206,924.070.006,924.07All992000000452451Bri999-999-10021JohnSmith580 Audubon AvenuePittsburghPA15228580 Audubon AvenuePittsburghPA1522840.37744-80.028712740° 22 ' 38.78 N080° 01' 43.36 W40° 22 ' 38.78 N080° 01' 43.36 Wabc@abc123.comBora Bora123478
2511/18/2020ServiceSale6255ZZZZZZMale08/26/20202010/29/20206,924.070.006,924.07All991001003879702Manager Elliot999-999-10022JohnSmith235 Foxwood RdWampumPA16157235 Foxwood RdWampumPA1615740.8188342-80.340175140° 49 ' 07.80 N080° 20' 24.63 W40° 49 ' 07.80 N080° 20' 24.63 Wabc@abc123.comBora Bora123479
Details
Cell Formulas
RangeFormula
AB2:AB25AB2=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:AC25AC2=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:Y25Y2=CONCATENATE(T2,V2,W2,X2)
 
Upvote 0
DistanceCalc.xlsm:

DistanceCalc.xlsm
ABCDEFGHIJ
1LatitudeLongitudeReference Test Values and Expected Results
2Origination Point 40° 24 ' 33.36 N080° 15' 22.69 WLatitudeLongitude
3Destination Point40° 26 ' 54.88 N079° 53' 48.64 W37~ 57' 3.7203" S144° 25' 29.5244" E
437° 39' 10.1561" S143~ 55' 35.3839" E
5Distance in Meters:30,813.3610Forward Azimuth ATAN2:81.712592Correct Results (distance in meters)
6
754972.271Bearing via ATAN2:307.0951
8Conversions to other measurements:
9To Statute Miles19.147
10To Nautical Miles16.638
11To Kilometers30.813
12To Feet101,093.704
13To Inches1,213,124.448
14
15To Furlong6,198,674.099
16To Cubit14,087.869
17To Palm2,311.002
18To Finger570.047
19To Step22,801.887
20To Pace45,603.774
21To Perch9,120.755
22To League14.006
23
24
25
26Note on Forward Azimuth:
27Forward Azimuth is the initial heading along the Great Circle path.
28Since the GC path is an arc across a sphere, it requires adjustments during the transit.
29For example, flying from Baghdad to Osaka, the Forward Azimuth is 60, but at the
30end of the trip the heading will be 120.
31This bearing is relative to True North and would no doubt require adjustments
32if using a magnetic compass.
33
DistanceByCoordinates
Cell Formulas
RangeFormula
B5B5=distvincenty(signit(B2),signit(C2),signit(B3),signit(C3))
D5D5=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)
B9B9=((B$5*39.3700787)/12)/5280
B10B10=((B$5*39.3700787)/12)/6076.11549
B11B11=B5/1000
B12B12=(B$5*39.3700787)/12
B13B13=B$5*39.3700787
B15B15=B5/0.00497096
B16B16=B5*0.4572
B17B17=B5*0.075
B18B18=B5*0.0185
B19B19=B5*0.74
B20B20=B5*1.48
B21B21=B5*0.296
B22B22=B5/2200
 
Upvote 0
MatchBack_Results.xlsx:

MatchBack_Results.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1Sold DateTypeTxn TypeIDModelSexBirth DateDays On HandArrvial DateOrig Sale PriceTxn PriceTxn DiscountTxn Ext PriceRegistryMicrochip NumberEmployeeContact NumberCustomer First NameCustomer Last NameCustomer Address 1Customer Address 2Customer CityCustomer StateCustomer Zip CodeConcatanated AddressLatitude1Longitude1LatDMSLongDMSDLatDMS-TxtDLongDMSCustomer EmailLocationInvoice NumberDistanceType
211/02/2020ServiceSale6105ZZZZZZMale07/21/20203909/24/20206,924.070.006,924.07All991001003714757Miyah999-999-9999Dionte VickersAmari Muhammad7441 Penfeild CtpittsburghPA152087441 Penfeild CtpittsburghPA1520840.448578-79.89684740° 26 ' 54.88 N079° 53' 48.64 W40° 26 ' 54.88 N079° 53' 48.64 Wabc@abc123.comBora Bora123456
311/03/2020ServiceSale6201ZZZZZZFemale08/17/20201410/20/20206,924.070.006,924.07All992000000403281Miyah999-999-10000JamesCrawford4205 Stratford DrIrwinPA156424205 Stratford DrIrwinPA1564240.294062-79.67272940° 17 ' 38.62 N079° 40' 21.82 W40° 17 ' 38.62 N079° 40' 21.82 Wabc@abc123.comBora Bora123457
411/03/2020ServiceSale6238ZZZZZZFemale08/21/2020710/27/20206,924.070.006,924.07All992000000403853Miyah999-999-10001JamesLauteri1012 Bomar ctMarsPA160461012 Bomar ctMarsPA1604640.69484-79.99121740° 41 ' 41.42 N079° 59' 28.38 W40° 41 ' 41.42 N079° 59' 28.38 Wabc@abc123.comBora Bora123458
511/04/2020ServiceSale6251ZZZZZZFemale08/29/2020610/29/20206,924.070.006,924.07All991001003718450Bri999-999-10002CayleyCoglio68 Boulder DrivePittsburghPA1523968 Boulder DrivePittsburghPA1523940.487673-79.78116140° 29 ' 15.62 N079° 46' 52.18 W40° 29 ' 15.62 N079° 46' 52.18 Wabc@abc123.comBora Bora123459
SalesMatch
Cell Formulas
RangeFormula
AB2:AB5AB2=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:AC5AC2=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:Y5Y2=CONCATENATE(T2,V2,W2,X2)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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