VLOOKUP Changed My Life

The number of people-hours lost to inefficient spreadsheet usage makes me shudder.

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”

A Table That Shows The UK Region For All Postcode Districts

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 Districts”