Nearest Postcode Search in Microsoft Excel
Published:
| by Julian Knight Reading time ~2 min.
📖 Posts
| 📎
Development, Microsoft, Windows
| 🔖
Maps, Office, VBA
I need to find a load of addresses by proximity to a postcode. I have the addresses in a Microsoft Access database.
- Download Paul Jenkins’s UK Postcode csv and import into Access as a table
- Create a query on your own table adding the following calculated field:
<pre>PCregion: Trim( Left( [My Table]![Postcode], InStr( [My Table]![Postcode], ” ” ) ) )</pre>
- Create a second query that joins the above query to the imported postcode table. Join on the “PCregion” field created above. Also add in the latitude and longitude fields from the postcodes table
- … Well, I couldn’t be bothered to fight with Access so I exported the key tables and used Excel instead! The principals are the same. Load the tables as tabs, load the Postcode data as a tab. Create a front sheet containing the reference Postcode (Create a Named Cell for this “STARTREGION”). Translate the reference Postcode to Latitude and Longitude by doing a VLOOKUP to the Postcode table, e.g.:
Where RC2 contains the reference Postcode and “UK_PostCodes!C1:C4” is the postcodes table (column 3 contains the Latitude and 4 the Longitude). Note that I’ve used R1C1 reference style as it is easier to mix fixed and relative references. Now use the same formula in the main data tables so that you have Lat. and Long. columns added based on the Postcode on each line. Finally add a “Distance” column to each data table with the following formula:
In this case, we’ve used the STARTREGION named cell and the Lat./Long. data is in columns 28 & 29 respectively. The calculation and Postcode reference data came from HM2K’s website: [How to search by nearest uk postcode in php][1]. [1]: http://www.hm2k.com/posts/how-to-search-by-nearest-uk-postcode-in-php
