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).
Step2: I created Ref Columns 1-5 to show which Zip Code is has the shortest distance:
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:
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:
Formula Worksheet:
Team Worksheet:
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 | 48 | 10017 | 48 | SMITH | ANDREW | SMITH | 10017 | |||||||||||||||||||||
| 3 | 143 Fargo CT | Vinita | CA | 92606 | 19 | 92692 | 19 | JONES | VINITA | JONES | 92692 | JONES | ||||||||||||||||||||
| 4 | 13 Callen St | Vinita | CA | 92817 | 30 | 92692 | 30 | JONES | VINITA | JONES | 92692 | JONES | ||||||||||||||||||||
| 5 | 2 Box Rd | Vinita | CA | 91932 | 125 | 92692 | 125 | Customer | VINITA | JONES | 92692 | JONES | ||||||||||||||||||||
| 6 | 500 West St | In Between | CA | 90712 | 50 | 91402 | 50 | APPLE | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 7 | 80 Finn Rd | In Between | CA | 90801 | 53 | 92692 | 53 | JONES | VINITA | JONES | 92692 | JONES | ||||||||||||||||||||
| 8 | 999 Cargo Rd | Thomas | CA | 90001 | 34 | 91402 | 34 | APPLE | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 9 | 3 Blue St | Thomas | CA | 93001 | 85 | 91402 | 85 | Customer | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 10 | 55 Hartford Ave | Thomas | CA | 91364 | 16 | 91402 | 16 | APPLE | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 11 | 3 Providence Ave | Thomas | CA | 91001 | 29 | 91402 | 29 | APPLE | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 12 | 5 June Rd | In Between | CA | 92364 | 300 | 92692 | 300 | Customer | VINITA | JONES | 92692 | JONES | ||||||||||||||||||||
| 13 | 90 Cutler Blvd | In Between | CA | 91714 | 51 | 91402 | 51 | APPLE | THOMAS | APPLE | 91402 | APPLE | ||||||||||||||||||||
| 14 | 143 Ohio Ave | Providence | RI | 02816 | 26 | 02809 | 26 | SMITH | DAVID | SMITH | 02809 | SMITH | ||||||||||||||||||||
| 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:
| 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 |
