JeremyLittman
New Member
- Joined
- Jan 2, 2013
- Messages
- 3
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:
<tbody>
</tbody>
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.
<tbody>
</tbody>
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.
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:
| Column A | Column B |
| (input) | (result) |
| CH1 4LQ | SP Energy Networks |
| WC1A 3AU | UK Power Network |
| YO5 4NP | Northern Powergrid |
| PA60 7AY | SSE Power |
<tbody>
</tbody>
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.
| FORMAT | EXAMPLE |
| AN NAA | M1 1AA |
| ANN NAA | M60 1NW |
| AAN NAA | CR2 6XH |
| AANN NAA | DN55 1PT |
| ANA NAA | W1A 1HQ |
| AANA NAA | EC1A 1BB |
<tbody>
</tbody>
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.
