+ Reply to Thread
Results 1 to 7 of 7

Closest distance between 2 zipcodes

  1. #1
    Registered User
    Join Date
    11-08-2014
    Location
    Cranberry Township, PA
    MS-Off Ver
    OFFICE 365
    Posts
    12

    Closest distance between 2 zipcodes

    Hi all

    In the sample file I am looking for the best way to find the closest distance to each zipcode in column F.
    Column A has the zipcodes of most US cities. Column F are all the zipcodes where we have a warehouse.
    If I'm in a zipcode in column A How can I find the closest warehouse in Column F?

    FYI:: There are 40000 rows in column A, B, C and 199 rows in column E, F.

    Any help is appreciated.
    Thank You
    Attached Files Attached Files
    Last edited by tonycel; 10-31-2021 at 03:41 PM.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,552

    Re: Closest distance between 2 zipcodes

    Your sample file link is reported as bad.

    Never mind ... for some reason it just worked.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,651

    Re: Closest distance between 2 zipcodes

    Administrative Note:

    Members will tailor the solutions they offer to the version of Office (Excel) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Closest distance between 2 zipcodes

    Thanks for the update.

    Now without some kind of table of distances I don't know how this can be done.

    Please include that in another upload in your next post?
    Dave

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,605

    Re: Closest distance between 2 zipcodes

    This post may help you with finding distances:

    https://www.excelforum.com/tips-and-...rivetimes.html

    Whether its Google or Bing, they will require an API key which I don't have (there may also be a charge for commercial use)

    Hope this is of use.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select “Solved” from the Thread Tools menu

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,367

    Re: Closest distance between 2 zipcodes

    Is it are you looking for?

    =LOOKUP(2,1/(ABS($F$2:$F$199-A2)=AGGREGATE(15,6,ABS($F$2:$F$199-A2),1)),$F$2:$F$199)
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-08-2014
    Location
    Cranberry Township, PA
    MS-Off Ver
    OFFICE 365
    Posts
    12

    Re: Closest distance between 2 zipcodes

    Thank you for all your help, that works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. X and Y coordinates, locating closest Point to point distance.
    By Erav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-13-2020, 04:02 PM
  2. Calculating total road distance and duration with google distance matrixes api
    By musta in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2019, 07:21 AM
  3. [SOLVED] Finding Closest Point and Distance
    By batman07 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2017, 08:06 AM
  4. Replies: 10
    Last Post: 07-20-2017, 12:16 PM
  5. [SOLVED] Retrieving the Closest Larger / Closest Smaller Values from a List
    By ChipsSlave in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-31-2017, 08:38 AM
  6. [SOLVED] Google Distance Matrix API - Distance and Time
    By rtcwlomax in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-18-2014, 08:42 PM
  7. Replies: 0
    Last Post: 08-25-2005, 02:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1