As well as debating politics, human rights and free speech, this blog is also interested in spreadsheets. Regular readers may recall my triumphant post about UK Postcode Areas, or the deeply honest and uncompromising account of the Excel spreadsheet function VLOOKUP.
As part of my job I’ve recently had cause to look up the bank name and address for a given set of sort codes. To begin with, I simply Googled each sort code and then copied-and-pasted whatever information was revealed.
This is extremely tiresome, so I’ve made myself a spreadsheet. I scraped the web for the information, and have put it in a handy CSV file: sortcodes-uk-and-ireland.csv [1.2 MB]. Continue reading “All the UK and Ireland Bank Sort Codes”
After my post about postcodes earlier this week, a couple of people asked me what a ‘lookup table’ was in that context. I thought I’d write something quick about their use in the context of spreadsheets, because they’re such a timesaver.
You can probably divide the world into people who have no idea about VLOOKUP tables in spreadsheets, and those who use them every day. Until recently I was firmly in the former category, but now I’m part of the latter group. Continue reading “VLOOKUP Changed My Life”
Are you analysing a bunch of addresses, and want to quickly group them into UK regions? That’s what I was doing recently, and did not find it trivially easy.
A quick visit to your favourite search engine will reveal a list of UK post code areas, and their corresponding towns. But that’s not actually as useful as it might seem. Many large towns and cities take their postcode from another large town or city, often in a different county. Basingstoke (Hampshire) has RG postcodes (Reading, Berkshire), for example.
In London, the problem is reversed. The capital has eight of its own postcodes, but the outer London boroughs have their own. Sorting a diverse list of postcodes does not immediately reveal which are ‘London’.
Sometimes it’s better to group locations by broader UK regions. That’s what I wanted to do with a list of over a thousand UK addresses. Eventually I found a site that (in the hope of selling you a handy map) groups all the postcodes by region. I was able to create a lookup table from that information, which I could then use to sort and count the number of addresses in each region. Continue reading “A Table That Shows The UK Region For All Postcode Areas”