How can I auto expand a list of UK postcode ranges for clean lookup

[JeremyLittman]

Hi there - I hope someone can help.

I have a very limited knowledge of VB with an intermediate knowledge of formulas and am using Excel 2011 for Mac.

I wish to create a spreadsheet that will enable me to lookup which electricity distribution network companies operate in specific regions in the UK based on a postcode search and am looking for some code that will save me hours of error prone work. I believe there are 2 main steps to this; an automated data clean up and a robust, partial postcode lookup function.

The lookup dataset I am using comprises of 14 columns for each Distribution Network containing partial postcode range e.g.

SP Energy Networks
CH 1-8
CW 1-11
L 1-39
L 41-49
L 60-70, 72
LL 11-49
LL 51-78
LL 7
PR 8, 9

PART 1

You will see there are combinations of 3 data types contained in the above example cells; ranges, lists and single codes. Rather than manually expanding the ranges and lists, I wish to automate the process so I end up with a complete expanded list of codes in each column e.g.

SP Energy Networks
CH 1
CH 2
CH 3
...
CW 1
CW 2
...
L 61
L 62
...
L 72
...
LL 7
PR 8
PR 9

PART 2

The expanded dataset in part 1 will only contain partial postcodes comprising of the area and district (see postcode format information below).

I will be looking up multiple lists of postcodes so wish to be able to achieve the following example query in a separate sheet:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD](input)[/TD]
[TD](result)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CH1 4LQ[/TD]
[TD]SP Energy Networks[/TD]
[/TR]
[TR]
[TD]WC1A 3AU[/TD]
[TD]UK Power Network[/TD]
[/TR]
[TR]
[TD]YO5 4NP[/TD]
[TD]Northern Powergrid[/TD]
[/TR]
[TR]
[TD]PA60 7AY[/TD]
[TD]SSE Power[/TD]
[/TR]
</tbody>[/TABLE]


There will be occasion when a postcode input contains more than 1 lookup result across different regions i.e. CW12 4NR will find both CW1 and CW12 in the source data. The lookup logic must therefore search each integer from 0 through to 99.

POSTCODE FORMAT

In order to understand the logic required for the lookup, a postcode is made up of the following elements:

PO1A 3AX

PO - the area. There are 124 postcode areas in the UK denoted by one or two alpha characters

1A - the district. There are approximately 20 Postcode districts in an area denoted by numbers ranging from 0 to 99. In London a further alpha character is used to divide some districts into sub divisions e.g. EC1A

3 - the sector. There are approximately 3000 addresses in a sector

AX - the unit. There are approximately 15 addresses per unit.


The following list shows all valid Postcode formats. "A" indicates an alphabetic character and "N" indicates a numeric character.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]FORMAT[/TD]
[TD]EXAMPLE[/TD]
[/TR]
[TR]
[TD]AN NAA[/TD]
[TD]M1 1AA[/TD]
[/TR]
[TR]
[TD]ANN NAA[/TD]
[TD]M60 1NW[/TD]
[/TR]
[TR]
[TD]AAN NAA[/TD]
[TD]CR2 6XH[/TD]
[/TR]
[TR]
[TD]AANN NAA[/TD]
[TD]DN55 1PT[/TD]
[/TR]
[TR]
[TD]ANA NAA[/TD]
[TD]W1A 1HQ[/TD]
[/TR]
[TR]
[TD]AANA NAA[/TD]
[TD]EC1A 1BB[/TD]
[/TR]
</tbody>[/TABLE]

The above postcode format information has been summarised from the following web source:

http://www.mrs.org.uk/pdf/postcodeformat.pdf

The Excel data file containing the postcode data can be downloaded from here:

https://dl.dropbox.com/u/47971826/Example excel/DNO Calculator.xlsx

Many thanks for your help, guidance or example code.