+ Reply to Thread
Results 1 to 2 of 2

Postcode to postcode distance checker

  1. #1
    Registered User
    Join Date
    02-21-2014
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    1

    Postcode to postcode distance checker

    Hi All,

    Really need your help!

    I have 40,000 postcodes that I need to compare against 22 postcodes to see which of the 22 is the nearest in distance to each of the 40k.

    I have a list of all the longitude and latitude codes for each of the 40,000 postcodes but i now need to know how to formulate a function in excel - if that is even possible.

    I am thinking that if i link to the internet and perform 900k look ups it will take forever/crash the system.

    Any ideas?

    Grateful in advance

    Kerry

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Postcode to postcode distance checker

    Hi and welcome to the forum!

    Assuming that the Latitude and Longitude for the first of your 40,000 for which you wish to obtain a result are in B2 and C2 respectively, try this array formula**:

    =INDEX(PostCode,MATCH(SMALL((SQRT(ABS(B2-Latitude)^2+ABS(C2-Longitude)^2)),1),SQRT(ABS(B2-Latitude)^2+ABS(C2-Longitude)^2),0))

    where you should replace PostCode, Latitude and Longitude with the appropriate ranges pertaining to the 22 in your actual set-up (e.g. replace PostCode by $X$2:$X$23, etc.).

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

+ 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. UK Postcode Distance Calculator
    By Pete_UK in forum Excel Tips
    Replies: 15
    Last Post: 10-18-2022, 05:01 AM
  2. Distance from Postcode
    By sitay1984 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2013, 07:12 AM
  3. Distance from Postcode
    By sitay1984 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-02-2013, 06:12 AM
  4. [SOLVED] Help needed trimming full postcode address to postcode sector.
    By Mikey7346 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 08-10-2012, 05:11 PM
  5. UK postcode distance calculation?
    By Steve Hawkins in forum Excel General
    Replies: 9
    Last Post: 04-06-2006, 08:35 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