Calculating shortest to longest distances between Zip Codes (using latitude and longitude)

pawcoyote · Jan 30, 2024

Hi, I have what I believe might be an interesting one I could use some help on.

I've have been working on formulas to show the distances (Miles) between multiple longitude and latitudes (Zip Codes). I am trying to find the shortest distance between multiple Zip Codes. Then put in order the shortest distance to longest distance. I would like to show who is assigned as the shortest distance to longest distances on the Planning worksheet. I tried to post all that I could think to help.

On the Formula worksheet I have the following formulas.

Step1: I created a Count Column that shows how many possible Zip Codes that could be included up to five (5).

Excel Formula:

=COUNTIF(TEAM!$A$2:$A$57,Planning!$E2)

Step2: I created Ref Columns 1-5 to show which Zip Code is has the shortest distance:

Excel Formula:

=IF(COLUMNS($G2:G2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=InventoryPlanning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:G2))))

Step3: I created a Distance formula that will show the distances: I also noticed that the distances are estimated not actual. I have tried the Mileage number: 3963.19 and Kilometer number: 6378.135:

Excel Formula:

=IF($G2="","",IF($E2=$N2,0,ACOS(COS(RADIANS(90-$L2))*COS(RADIANS(90-$C2))+SIN(RADIANS(90-$L2))*SIN(RADIANS(90-$C2))*COS(RADIANS($M2-$D2)))*6378.135))

This formula is successfully returning the correct distances in Distance-1 thru Distance-5. But the the Ref#’s are not showing in the order of shortest distance. It is shown by the State and how I have them loaded on the TEAM worksheet. How can I change the formula above to show the shortest distance in order within the Ref Columns? I would really like to do away with looking at the State first and just look at the shortest distance between the Zip Codes (Latitude/Longitudes). That way I do not have to worry about loading States into the Team sheet just the people and their locations.

Can anyone help? Sorry for the complicated problem in advance.

Planning Worksheet:

Zip Code Distances.xlsm

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

1

Location Number

Location Name

Address

CITY

STATE

ZIP CODE

Min - Dist

Min Zip

Distance

Ricoh / Client

First Name

Last Name

Zip

Ref2 - Distance

Ref2 - Name

Ref2 - Zip Code

Ref3 - Distance

Ref3 - Name

Ref3 - Zip Code

Ref4 - Distance

Ref4 - Name

Ref4 - Zip Code

Ref5 - Distance

Ref5 - Name

Ref5 - Zip Code

SSSIG

Distance Allowed: Enter Here ---->

65

Client Name: Enter Here ----->

Customer

2

123 Smith Street

MELVILLE

NY

11747

3

143 Fargo CT

Vinita

CA

92606

4

13 Callen St

Vinita

CA

92817

5

2 Box Rd

Vinita

CA

91932

6

500 West St

In Between

CA

90712

7

80 Finn Rd

In Between

CA

90801

8

999 Cargo Rd

Thomas

CA

90001

9

3 Blue St

Thomas

CA

93001

10

55 Hartford Ave

Thomas

CA

91364

11

3 Providence Ave

Thomas

CA

91001

12

5 June Rd

In Between

CA

92364

13

90 Cutler Blvd

In Between

CA

91714

14

143 Ohio Ave

Providence

RI

02816

15

99 June LN

Okalahoma City

OK

73108

55,000

Customer

16

74 Washington St

Albuquerque

NM

87109

55,000

Customer

17

26 Morgan Rd

Bessemer

AL

35022

55,000

Customer

Planning

Cell Formulas
Range	Formula
G2:G17	G2	=MIN(Formulas!O2,Formulas!S2,Formulas!W2,Formulas!AA2,Formulas!AE2)
H2:H17	H2	=IF($G2=Formulas!$O2,Formulas!$G2,IF($G2=Formulas!$S2,Formulas!$H2,IF($G2=Formulas!$W2,Formulas!$I2,IF($G2=Formulas!$AA2,Formulas!$J2,IF($G2=Formulas!$AE2,Formulas!$K2,"")))))
I2:I17	I2	=IF(G2<5000,G2,"")
J2:J17	J2	=IF($I2<$AB$1,$L2,$AD$1)
K2:K17	K2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$C$2:$C$88,"",0)
L2:L17	L2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$D$2:$D$88,"",0)
M2:M17	M2	=XLOOKUP($H2,TEAM!$B$2:$B$88,TEAM!$B$2:$B$88,"",0)
Z2:Z17	Z2	=XLOOKUP($H2,TERRITORY!$B$2:$B$86,TERRITORY!$D$2:$D$86,"",0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J:J	Cell Value	contains "Customer"	text	NO
A:A	Cell Value	duplicates	text	NO
I1:I17,K2:Z17,K1:M1,Z1	Cell Value	=""	text	NO

Formula Worksheet:

Zip Code Distances.xlsm

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

1

STATE

ZIPCODE

LAT

LONG

LAT+LONG

COUNT

Ref1

Ref2

Ref3

Ref4

Ref5

LAT

LONG

LAT+LONG

DISTANCE-1

LAT

LONG

LAT+LONG

DISTANCE-2

LAT

LONG

LAT+LONG

DISTANCE-3

LAT

LONG

LAT+LONG

DISTANCE-4

LAT

LONG

LAT+LONG

DISTANCE-5

2

3

4

5

6

7

8

9

10

11

12

13

Formulas

Cell Formulas
Range	Formula
A2:B13	A2	=Planning!E2
C2:C13	C2	=XLOOKUP(Planning!F2, ZipCodeDB!$B$2:$B$50000, ZipCodeDB!$G$2:$G$50000,,0)
D2:D13	D2	=XLOOKUP(Planning!$F2, ZipCodeDB!$B$2:$B$50000, ZipCodeDB!$H$2:$H$50000,,0)
E2:E13	E2	=IFERROR($C2+$D2,"")
F2:F13	F2	=COUNTIF(TEAM!$A$2:$A$57,Planning!$E2)
G2:G13	G2	=IF(COLUMNS($G2:G2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:G2))))
H2:H13	H2	=IF(COLUMNS($G2:$H2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$H2))))
I2:I13	I2	=IF(COLUMNS($G2:$I2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$I2))))
J2:J13	J2	=IF(COLUMNS($G2:$J2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$J2))))
K2:K13	K2	=IF(COLUMNS($G2:$K2)>$F2,"",INDEX(TEAM!$B$2:$B$80,SMALL(IF(TEAM!$A$2:$A$80=Planning!$E2,ROW(TEAM!$B$2:$B$80)-ROW(TEAM!$B$2)+1),COLUMNS($G2:$K2))))
L2:L13	L2	=XLOOKUP($G2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
M2:M13	M2	=XLOOKUP($G2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
N2:N13	N2	=IF($G2="","",SUM($L2+$M2))
O2:O13	O2	=IF($G2="","",IF($E2=$N2,0,ACOS(COS(RADIANS(90-$L2))COS(RADIANS(90-$C2))+SIN(RADIANS(90-$L2))SIN(RADIANS(90-$C2))COS(RADIANS($M2-$D2)))6378.135))
P2:P13	P2	=XLOOKUP($H2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
Q2:Q13	Q2	=XLOOKUP($H2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
R2:R13	R2	=IF($H2="","",SUM($P2+$Q2))
S2:S13	S2	=IF($H2="",55000,IF($E2=$R2,0,ACOS(COS(RADIANS(90-$P2))COS(RADIANS(90-$C2))+SIN(RADIANS(90-$P2))SIN(RADIANS(90-$C2))COS(RADIANS($Q2-$D2)))6378.135))
T2:T13	T2	=XLOOKUP($I2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
U2:U13	U2	=XLOOKUP($I2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
V2:V13	V2	=IF($I2="","",SUM($T2+$U2))
W2:W13	W2	=IF($I2="",55000,IF($E2=$V2,0,ACOS(COS(RADIANS(90-$T2))COS(RADIANS(90-$C2))+SIN(RADIANS(90-$T2))SIN(RADIANS(90-$C2))COS(RADIANS($U2-$D2)))6378.135))
X2:X13	X2	=XLOOKUP($J2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
Y2:Y13	Y2	=XLOOKUP($J2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
Z2:Z13	Z2	=IF($J2="","",SUM($X2+$Y2))
AA2:AA13	AA2	=IF($J2="",55000,IF($E2=$Z2,0,ACOS(COS(RADIANS(90-$X2))COS(RADIANS(90-$C2))+SIN(RADIANS(90-$X2))SIN(RADIANS(90-$C2))COS(RADIANS($Y2-$D2)))6378.135))
AB2:AB13	AB2	=XLOOKUP($K2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$G$2:$G$50000,"",0)
AC2:AC13	AC2	=XLOOKUP($K2, ZipCodeDB!$B$2:$B$50000,ZipCodeDB!$H$2:$H$50000,"",0)
AD2:AD13	AD2	=IF($K2="","",SUM($AB2+$AC2))
AE2:AE13	AE2	=IF($K2="",55000,IF($E2=$AD2,0,ACOS(COS(RADIANS(90-$AB2))COS(RADIANS(90-$C2))+SIN(RADIANS(90-$AB2))SIN(RADIANS(90-$C2))COS(RADIANS($AC2-$D2)))6378.135))

Team Worksheet:

Book1

A

B

C

D

1

STATE COVERAGE

ZIP

FIRST NAME

LAST NAME

2

AZ

85283

STEVEN

JONES

3

AZ

85085

ROBERT

SMITH

4

CA

91402

THOMAS

APPLE

5

CA

92692

VINITA

JONES

6

CA

94507

SHANE

SMITH

7

CO

80015

THERESA

JONES

8

CO

80550

CASEY

SMITH

9

CT

02861

BOB

SMITH

10

CT

06482

THOMAS

SMITH

11

CT

02809

DAVID

SMITH

12

GA

33019

BILL

SMITH

13

IL

46311

JOB

SMITH

14

IL

60563

BYRON

SMITH

15

IL

60610

STEVE

JONES

16

IN

46311

JOB

SMITH

17

KY

41091

MARCUS

SMITH

18

MA

02861

BOB

SMITH

19

MA

02809

DAVID

SMITH

20

ME

02861

BOB

SMITH

21

MI

48323

JOEL

SMITH

22

MI

46311

JOB

SMITH

23

MO

64118

LOUIE

SMITH

24

NC

28031

JAMES

SMITH

25

NH

02861

BOB

SMITH

26

NJ

19440

LARRY

SMITH

27

NV

89431

JEFFREY

SMITH

28

NY

10017

ANDREW

SMITH

29

NY

06482

THOMAS

SMITH

30

NY

19440

LARRY

SMITH

31

NY

02809

DAVID

SMITH

32

NY

02861

BOB

SMITH

33

OH

45420

ANDRE

SMITH

34

OH

48323

JOEL

SMITH

35

PA

19440

LARRY

SMITH

36

PA

19006

DAVID

SMITH

37

PA

19341

ERIC

SMITH

38

PA

19341

BRIAN

SMITH

39

RI

02861

BOB

SMITH

40

RI

02809

DAVID

SMITH

41

TX

77382

BRANDON

SMITH

Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	Cell Value	duplicates	text	NO

KRice · Feb 16, 2024

No problem...just post back. Glad you are feeling better.

pawcoyote · Feb 16, 2024

KRice said:
This is what my question was getting at. Given some baseline location whose lat/long are shown in column B of the Formulas worksheet, you want to determine which are the five closest zip codes that appear in the Team worksheet. And the ZipCodeDB worksheet is needed as a cross reference to provide lat/long for any zip codes in the Team worksheet. Is that correct? That is what the formula I offered earlier does. Have a look at the results returned. If that is what you want, then the output of that array can be adjusted to deliver just the content desired. You don't need the Formulas worksheet at all, as the results can be obtained using the input zip code in column F of the Planning worksheet, and then after developing the results that depend on the ZipCodeDB and TEAM worksheets, the final results can be spilled across a row in the Planning worksheet, as shown in the slightly reworked version below. Then some other formulas in the columns G:J range may not be needed (note that the min formula in column G isn't referencing the correct cells right now).

MrExcel_20240131_C (version 1).xlsx
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
1 Address CITY STATE ZIP CODE Min - Dist Min Zip Distance Ricoh / Client Distance First Name Last Name Zip Distance First Name Last Name Zip Distance First Name Last Name Zip Distance First Name Last Name Zip Distance First Name Last Name Zip
2 123 Smith Street MELVILLE NY 11747 69.578899 6482 69.578899 Customer 48.09604 ANDREW SMITH 10017 69.578899 THOMAS SMITH 6482 170.28655 LARRY SMITH 19440 204.25017 DAVID SMITH 2809 209.53438 BOB SMITH 2861
Planning
Cell Formulas
Range Formula
G2 G2 =MIN(Formulas!O2,Formulas!S2,Formulas!W2,Formulas!AA2,Formulas!AE2)
H2 H2 =IF($G2=Formulas!$O2,Formulas!$G2,IF($G2=Formulas!$S2,Formulas!$H2,IF($G2=Formulas!$W2,Formulas!$I2,IF($G2=Formulas!$AA2,Formulas!$J2,IF($G2=Formulas!$AE2,Formulas!$K2,"")))))
I2 I2 =IF(G2<5000,G2,"")
J2 J2 =IF($I2<$AG$1,$M2,$AI$1)
K2:AD2 K2 =LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,TEAM!$B$2:$B$80, fname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$C$2:$C$80), lname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$D$2:$D$80), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,""), res,TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5)), res)
Dynamic array formulas.

I brought the code into Column K and it spills across K-AD. Looks really good. Looking at how to update G-H now. Also, to change to Mile I just need to update the calculation to be 3963.19 instead of the 6378.135, correct?

pawcoyote · Feb 17, 2024

One thing I am seeing is if the Zip Code in G is either blank or has a 00000, it shows nothing in K-N but it will add people in all the other cell for O-AD. Looking into how to show nothing in the cells if the Zip Code in G is either not there or listed as 00000.

Excel Formula:

=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000,
basezip,F48, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong),
zip,COETeam!$B$2:$B$69, fname,XLOOKUP(zip,COETeam!$B$2:$B$69,COETeam!$C$2:$C$69), lname,XLOOKUP(zip,COETeam!$B$2:$B$69,COETeam!$D$2:$D$69),
lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong),
dist,IFERROR(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*3963.19,""),
res,TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5)), res)

pawcoyote · Feb 18, 2024

@KRice

I updated the code and it looks to be working nicely. The only thing is when there is nothing or 00000 in Column F the spill cells bring in data that should not be there. I would like it to show blanks unless a Zip Code is entered into column F. J - M work fine.. Any suggestions?

COE_Zip Code Distance Locations and Planning DRAFT021724.xlsm

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

Location Number

Location Name

Address

CITY

STATE

ZIP CODE

Min - Dist

Min Zip

Ricoh / Client

Ref1 - Distance

Ref1 - First Name

Ref1 - Last Name

Ref1 - Zip Code

Ref2 - Distance

Ref2 - First Name

Ref2 - Last Name

Ref2 - Zip Code

Ref3 - Distance

Reft3 - First Name

Ref3 - Last Name

Ref3 - Zip Code

Ref4 - Distance

Ref4 - First Name

Ref4 - Last Name

Ref4 - Zip Code

Ref5 - Distance

Ref5 - First Name

Ref5 - Last Name

Ref5 - Zip Code

SSSIG

Distance Allowed: Enter Here ---->

65

Client Name: Enter Here ----->

Customer

2

123 Smith Street

MELVILLE

NY

11747

ANDREW

SMITH

10017

43

THOMAS

SMITH

06482

99

DAVID

SMITH

19006

106

LARRY

SMITH

19440

127

DAVID

SMITH

02809

3

143 Fargo CT

Vinita

CA

92606

VINITA

JONES

92692

51

THOMAS

APPLE

91402

330

ROBERT

SMITH

85085

340

STEVEN

JONES

85283

372

SHANE

SMITH

94507

JONES

4

13 Callen St

Vinita

CA

92817

VINITA

JONES

92692

46

THOMAS

APPLE

91402

328

ROBERT

SMITH

85085

339

STEVEN

JONES

85283

365

SHANE

SMITH

94507

JONES

5

2 Box Rd

Vinita

CA

91932

VINITA

JONES

92692

138

THOMAS

APPLE

91402

302

ROBERT

SMITH

85085

306

STEVEN

JONES

85283

458

SHANE

SMITH

94507

JONES

6

143 Ohio Ave

Providence

RI

02904

BOB

SMITH

02861

15

DAVID

SMITH

02809

99

THOMAS

SMITH

06482

152

ANDREW

SMITH

10017

224

DAVID

SMITH

19006

SMITH

7

99 June LN

Okalahoma City

RI

02703

BOB

SMITH

02861

17

DAVID

SMITH

02809

107

THOMAS

SMITH

06482

161

ANDREW

SMITH

10017

232

DAVID

SMITH

19006

SMITH

8

74 Washington St

Albuquerque

NM

87109

THERESA

JONES

80015

329

ROBERT

SMITH

85085

330

STEVEN

JONES

85283

380

CASEY

SMITH

80550

580

TERRANCE

SMITH

76065

9

00000

5265

DAVID

SMITH

02809

5273

BOB

SMITH

02861

5362

THOMAS

SMITH

06482

5390

ANDREW

SMITH

10017

10

0

00000

0

00000

5265

DAVID

SMITH

02809

5273

BOB

SMITH

02861

5362

THOMAS

SMITH

06482

5390

ANDREW

SMITH

10017

11

0

00000

0

00000

5265

DAVID

SMITH

02809

5273

BOB

SMITH

02861

5362

THOMAS

SMITH

06482

5390

ANDREW

SMITH

10017

Planning

Cell Formulas
Range	Formula
G2:G11	G2	=MIN(Planning!J2, Planning!N2, Planning!R2, Planning!V2, Planning!Z2)
H2:H11	H2	=IF($G2=Planning!$J2, Planning!$M2, IF($G2=Planning!$N2, Planning!$Q2, IF($G2=Planning!$R2, Planning!$U2, IF($G2=Planning!$V2, Planning!$Y2, IF($G2=Planning!$Z2, Planning!$AC2, "") ) ) ) )
I2:I11	I2	=IF($G2<$AF$1,$L2,$AH$1)
J2:AC11	J2	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,TEAM!$B$2:$B$65, fname,XLOOKUP(zip,TEAM!$B$2:$B$65,TEAM!$C$2:$C$65), lname,XLOOKUP(zip,TEAM!$B$2:$B$65,TEAM!$D$2:$D$65), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ACOS(COS(RADIANS(90-lat))COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))SIN(RADIANS(90-baselat))COS(RADIANS(long-baselong)))3963.19,""), res,TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5)), res)
AD2:AD8,AD10	AD2	=XLOOKUP($H2,TERRITORY!$B$2:$B$86,TERRITORY!$D$2:$D$86,"",0)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:J	Cell Value	contains "Customer"	text	NO
A:A	Cell Value	duplicates	text	NO
K1:M1,AD1,K2:AD11	Cell Value	=""	text	NO

KRice · Feb 18, 2024

Sounds as if a simple check for an input is needed…should be easy to address. I’ll have a look when I have a moment at the computer. Have you noted any other issues? Are there any instances where a ZIP code might be entered that does not exist in the DB reference?

pawcoyote · Feb 18, 2024

KRice said:
Sounds as if a simple check for an input is needed…should be easy to address. I’ll have a look when I have a moment at the computer. Have you noted any other issues? Are there any instances where a ZIP code might be entered that does not exist in the DB reference?

Thanks and not as of yet. So far I have been running a lot of addresses to see what happens.

KRice · Feb 18, 2024

A comment and a question:
The Min Distance/ZIP will always be found in the Ref1 columns J/M because the main formula that spills the results uses a SORT function to sort all of the computed distances by distance, and then to "TAKE" only the smallest 5 in that order. So the col G & H formulas are not necessary. You could rely on cols J & M, or replace the G & H formula with simpler =J2 and =M2 formulas.

You mention something about column F either being blank or 00000. How are you making "00000" appear? Is that done with custom/special formatting, perhaps using Excel's built-in ZIP code format offering? If so, then the ZIP codes are numeric and not text. I just wanted to confirm that there is consistency between your large DB table, and any other ZIP codes used. An easy check can be made for a cell with the Special ZIP formatting...entering a single 0 results in 00000. For testing purposes, I've assumed this is the case and applied special formatting for "ZIP code" for all ZIP fields on the Planning and mocked-up TEAM and ZipCodeDB worksheets.

Now something interesting happens, depending on what is entered in column F. If 0 is entered with the special ZIP formatting, we'll see 00000, and because XLOOKUP cannot find such a ZIP, an #N/A error is returned, so we will not have a baseline lat/long (only #N/A errors for each), and other functions that depend on the baseline lat/long will fail to return anything meaningful. In this case, the distance array returned by the formula is all blanks, so there is nothing to sort, and the names displayed are the first five in the list on the TEAM worksheet.

However, if nothing is entered in column F, we'll see nothing...a blank cell...,because the lookup range in the XLOOKUP functions is larger than the bottom row where data are found, XLOOKUP finds the first blank below the last ZIP code and returns the corresponding lat/long, which are also blank. These blanks are interpreted as 0's, so the baseline lat/long for blank ZIP codes is 0/0, a location sometimes called Null Island in the Gulf of Guinea of the coast of West Africa. A weather buoy called the Soul buoy is moored there. So all distances are computed relative to this location in the middle of the ocean!

But there is one more scenario to consider: perhaps a value is entered in column F that does not match any ZIP codes in ZipCodeDB. That also results in an #N/A error and the results are the same as the first scenario above.

None of these scenarios should result in a meaningful result, so some error trapping is appropriate. I've added this to the result part of the formula: IF(OR(basezip=0,ISERROR(baselat))
...followed by an expression to deliver a blank row if TRUE: EXPAND("",,20,""). The basezip component addresses the first scenario and the ISERROR component addresses the other two.

MrExcel_20240131_C (version 1).xlsx

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

1

CITY

STATE

ZIP CODE

Min - Dist

Min Zip

Ricoh / Client

Ref1 - Distance

Ref1 - First Name

Ref1 - Last Name

Ref1 - Zip

Ref2 - Distance

Ref2 - First Name

Ref2 - Last Name

Ref2 - Zip

2

MELVILLE

NY

11747

ANDREW

SMITH

10017

70

THOMAS

SMITH

06482

3

Vinita

CA

92606

VINITA

JONES

92692

83

THOMAS

APPLE

91402

4

Vinita

CA

92817

VINITA

JONES

92692

73

THOMAS

APPLE

91402

5

Vinita

CA

91932

VINITA

JONES

92692

221

THOMAS

APPLE

91402

6

In Between

CA

90712

THOMAS

APPLE

91402

53

VINITA

JONES

92692

7

In Between

CA

00000

Customer

8

Thomas

CA

Customer

9

Thomas

CA

93001

Customer

10

Thomas

CA

91364

Customer

11

Thomas

CA

91001

THOMAS

APPLE

91402

79

VINITA

JONES

92692

12

In Between

CA

92364

VINITA

JONES

92692

321

THOMAS

APPLE

91402

13

In Between

CA

91714

THOMAS

APPLE

91402

54

VINITA

JONES

92692

14

Providence

RI

02816

DAVID

SMITH

02809

28

BOB

SMITH

02861

15

Okalahoma City

OK

73108

VINITA

JONES

92692

1910

THOMAS

APPLE

91402

16

Albuquerque

NM

87109

VINITA

JONES

92692

1091

THOMAS

APPLE

91402

17

Bessemer

AL

35022

LARRY

SMITH

19440

1418

ANDREW

SMITH

10017

Planning

Cell Formulas
Range	Formula
G2:G17	G2	=J2
H2:H17	H2	=M2
I2:I17	I2	=IF($G2<$AF$1,$L2,$AH$1)
J2:AC17	J2	=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,TEAM!$B$2:$B$80, fname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$C$2:$C$80), lname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$D$2:$D$80), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))SIN(RADIANS(90-baselat))COS(RADIANS(long-baselong)))6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )
Dynamic array formulas.

pawcoyote · Feb 20, 2024

KRice said:
A comment and a question:
The Min Distance/ZIP will always be found in the Ref1 columns J/M because the main formula that spills the results uses a SORT function to sort all of the computed distances by distance, and then to "TAKE" only the smallest 5 in that order. So the col G & H formulas are not necessary. You could rely on cols J & M, or replace the G & H formula with simpler =J2 and =M2 formulas.

You mention something about column F either being blank or 00000. How are you making "00000" appear? Is that done with custom/special formatting, perhaps using Excel's built-in ZIP code format offering? If so, then the ZIP codes are numeric and not text. I just wanted to confirm that there is consistency between your large DB table, and any other ZIP codes used. An easy check can be made for a cell with the Special ZIP formatting...entering a single 0 results in 00000. For testing purposes, I've assumed this is the case and applied special formatting for "ZIP code" for all ZIP fields on the Planning and mocked-up TEAM and ZipCodeDB worksheets.

Now something interesting happens, depending on what is entered in column F. If 0 is entered with the special ZIP formatting, we'll see 00000, and because XLOOKUP cannot find such a ZIP, an #N/A error is returned, so we will not have a baseline lat/long (only #N/A errors for each), and other functions that depend on the baseline lat/long will fail to return anything meaningful. In this case, the distance array returned by the formula is all blanks, so there is nothing to sort, and the names displayed are the first five in the list on the TEAM worksheet.

However, if nothing is entered in column F, we'll see nothing...a blank cell...,because the lookup range in the XLOOKUP functions is larger than the bottom row where data are found, XLOOKUP finds the first blank below the last ZIP code and returns the corresponding lat/long, which are also blank. These blanks are interpreted as 0's, so the baseline lat/long for blank ZIP codes is 0/0, a location sometimes called Null Island in the Gulf of Guinea of the coast of West Africa. A weather buoy called the Soul buoy is moored there. So all distances are computed relative to this location in the middle of the ocean!

But there is one more scenario to consider: perhaps a value is entered in column F that does not match any ZIP codes in ZipCodeDB. That also results in an #N/A error and the results are the same as the first scenario above.

None of these scenarios should result in a meaningful result, so some error trapping is appropriate. I've added this to the result part of the formula: IF(OR(basezip=0,ISERROR(baselat))
...followed by an expression to deliver a blank row if TRUE: EXPAND("",,20,""). The basezip component addresses the first scenario and the ISERROR component addresses the other two.

MrExcel_20240131_C (version 1).xlsx
D E F G H I J K L M N O P Q
1 CITY STATE ZIP CODE Min - Dist Min Zip Ricoh / Client Ref1 - Distance Ref1 - First Name Ref1 - Last Name Ref1 - Zip Ref2 - Distance Ref2 - First Name Ref2 - Last Name Ref2 - Zip
2 MELVILLE NY 11747 48 10017 SMITH 48 ANDREW SMITH 10017 70 THOMAS SMITH 06482
3 Vinita CA 92606 19 92692 JONES 19 VINITA JONES 92692 83 THOMAS APPLE 91402
4 Vinita CA 92817 30 92692 JONES 30 VINITA JONES 92692 73 THOMAS APPLE 91402
5 Vinita CA 91932 125 92692 Customer 125 VINITA JONES 92692 221 THOMAS APPLE 91402
6 In Between CA 90712 50 91402 APPLE 50 THOMAS APPLE 91402 53 VINITA JONES 92692
7 In Between CA 00000 Customer
8 Thomas CA Customer
9 Thomas CA 93001 Customer
10 Thomas CA 91364 Customer
11 Thomas CA 91001 29 91402 APPLE 29 THOMAS APPLE 91402 79 VINITA JONES 92692
12 In Between CA 92364 300 92692 Customer 300 VINITA JONES 92692 321 THOMAS APPLE 91402
13 In Between CA 91714 51 91402 APPLE 51 THOMAS APPLE 91402 54 VINITA JONES 92692
14 Providence RI 02816 26 02809 SMITH 26 DAVID SMITH 02809 28 BOB SMITH 02861
15 Okalahoma City OK 73108 1850 92692 Customer 1850 VINITA JONES 92692 1910 THOMAS APPLE 91402
16 Albuquerque NM 87109 1031 92692 Customer 1031 VINITA JONES 92692 1091 THOMAS APPLE 91402
17 Bessemer AL 35022 1295 19440 Customer 1295 LARRY SMITH 19440 1418 ANDREW SMITH 10017
Planning
Cell Formulas
Range Formula
G2:G17 G2 =J2
H2:H17 H2 =M2
I2:I17 I2 =IF($G2<$AF$1,$L2,$AH$1)
J2:AC17 J2 =LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, basezip,F2, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,TEAM!$B$2:$B$80, fname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$C$2:$C$80), lname,XLOOKUP(zip,TEAM!$B$2:$B$80,TEAM!$D$2:$D$80), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )
Dynamic array formulas.

Hi,
The 00000 is if a zero is added by mistake into column F it the formatting will make it a Zip Code format which is 00000. Never knew about the Bouy that is interesting. Hate to ask this but would this have been easier to build in VBA? I am going to add the new code and it to my sheet and see what happens. Will let you know. Thank you for all the help and guidance.

KRice · Feb 20, 2024

Thanks for the update. VBA is always an option, although for some, that may be problem (e.g., IT restrictions that limit active code, or the skill set needed for implementing and maintaining the code). In this case, the somewhat complicated looking formula isn't too difficult to follow: several variable assignments based on your worksheet structure, lookups to obtain the lat/long for the baseline location, lookup to obtain the lat/long for known "team" zip codes, and then a distance calculation for every team member's location relative to the baseline location, followed by a final formula that sorts the results, takes the shortest five, and formats the results for output.

Now that I've looked at this again, there is an easier way to make the assignments for the first name (fname) and last name (lname) arrays. The revised formula:

Excel Formula:

=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80,
basezip,F3, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong),
zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3),
lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong),
dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""),
res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )

pawcoyote · Feb 20, 2024

KRice said:
=LET(dbzip,ZipCodeDB!$B$2:$B$50000, dblat,ZipCodeDB!$G$2:$G$50000, dblong,ZipCodeDB!$H$2:$H$50000, team,TEAM!$B$2:$D$80, basezip,F3, baselat,XLOOKUP(basezip,dbzip,dblat), baselong,XLOOKUP(basezip,dbzip,dblong), zip,CHOOSECOLS(team,1), fname,CHOOSECOLS(team,2), lname,CHOOSECOLS(team,3), lat,XLOOKUP(zip,dbzip,dblat), long,XLOOKUP(zip,dbzip,dblong), dist,IFERROR(ROUND(ACOS(COS(RADIANS(90-lat))*COS(RADIANS(90-baselat))+SIN(RADIANS(90-lat))*SIN(RADIANS(90-baselat))*COS(RADIANS(long-baselong)))*6378.135,0),""), res,IF(OR(basezip=0,ISERROR(baselat)),EXPAND("",,20,""),TOROW(TAKE(SORT(UNIQUE(HSTACK(dist,fname,lname,zip)),1,1),5))), res )

This worked great as well. Change F3 to F2 and it worked great. You changed to look at the Columns on the team sheet that is neat. I have always wondered if what is cleaner, formulas or VBA. I am learning about VBA as well. I also changed from kilometers to miles in the RADIANS calculation.

Calculating shortest to longest distances between Zip Codes (using latitude and longitude)

pawcoyote

Board Regular

KRice

Well-known Member

Excel Facts

pawcoyote

Board Regular

pawcoyote

Board Regular

pawcoyote

Board Regular

KRice

Well-known Member

pawcoyote

Board Regular

KRice

Well-known Member

pawcoyote

Board Regular

KRice

Well-known Member

pawcoyote

Board Regular

Similar threads

Forum statistics

Share this page

Calculating shortest to longest distances between Zip Codes (using latitude and longitude)

Board Regular

Well-known Member

Excel Facts

Board Regular

Board Regular

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock