rogermccarthy
New Member
- Joined
- Nov 1, 2004
- Messages
- 2
Hi,
I have a long spreadsheet showing deaths by UK postcode sector of which there are 8,411 but want to compare this to another spreadsheet giving census religion by postcode area which is the top level of postcode and of which there are 105 with data.
The sector sheet looks likes this with AL1 1 being the first column and the 34 being the 2010 deaths in the second:
Postcode sector 2010
AL1 1 34
AL1 2 68
AL1 3 36
AL1 4 54
AL1 5 48
AL10 0 94
AL10 8 136
AL10 9 65
AL2 1 50
AL2 2 50
AL2 3 67
AL3 4 59
AL3 5 77
AL3 6 31
AL3 7 39
AL3 8 40
AL4 0 44
AL4 8 48
AL4 9 87
AL5 1 35
AL5 2 32
AL5 3 32
AL5 4 53
AL5 5 53
AL6 0 35
AL6 9 63
AL7 1 45
AL7 2 45
AL7 3 80
AL7 4 100
AL8 6 56
AL8 7 67
AL9 5 27
AL9 6 8
AL9 7 58
B1 1 8
B1 2 13
B1 3 0
B10 0 42
B10 9 93
and so on for 8,000 more rows
The postcode area spreadsheet looks like this with the AL being the area in column 1 and 250,427 being the first column of data giving numbers for each religion in the area
All categories: Religion
AL 250,427
B 1,904,658
BA 434,166
BB 488,917
BD 578,336
BH 551,987
and so on for a total 105 rows
Problem is that the postcode areas are not just the first two digits of the postcode sector as some are just one digit (i.e. AL and B in the example are both areas) so I can't do a simple split at third digit or space.
However all postcode areas start with one or two letters and all postcode districts and sectors each add a number (when you get down to units start re-appearing again my own postcode for instance is TN (area) 6 (district) 2 (sector) and DN (unit)
I can subtotal them one by one within a filter (e.g. by selecting filter using starts with AL) but this means doing 105 separate operations which is a pain
Is there a formula which allows me to generate subtotals for anything in column 2 which starts with a letter in column 1 but fixes the problem of there being both two and one letter postcode areas?
e.g. I just want subtotals for AL and B and BA etc in the example above.
Thanks,
I have a long spreadsheet showing deaths by UK postcode sector of which there are 8,411 but want to compare this to another spreadsheet giving census religion by postcode area which is the top level of postcode and of which there are 105 with data.
The sector sheet looks likes this with AL1 1 being the first column and the 34 being the 2010 deaths in the second:
Postcode sector 2010
AL1 1 34
AL1 2 68
AL1 3 36
AL1 4 54
AL1 5 48
AL10 0 94
AL10 8 136
AL10 9 65
AL2 1 50
AL2 2 50
AL2 3 67
AL3 4 59
AL3 5 77
AL3 6 31
AL3 7 39
AL3 8 40
AL4 0 44
AL4 8 48
AL4 9 87
AL5 1 35
AL5 2 32
AL5 3 32
AL5 4 53
AL5 5 53
AL6 0 35
AL6 9 63
AL7 1 45
AL7 2 45
AL7 3 80
AL7 4 100
AL8 6 56
AL8 7 67
AL9 5 27
AL9 6 8
AL9 7 58
B1 1 8
B1 2 13
B1 3 0
B10 0 42
B10 9 93
and so on for 8,000 more rows
The postcode area spreadsheet looks like this with the AL being the area in column 1 and 250,427 being the first column of data giving numbers for each religion in the area
All categories: Religion
AL 250,427
B 1,904,658
BA 434,166
BB 488,917
BD 578,336
BH 551,987
and so on for a total 105 rows
Problem is that the postcode areas are not just the first two digits of the postcode sector as some are just one digit (i.e. AL and B in the example are both areas) so I can't do a simple split at third digit or space.
However all postcode areas start with one or two letters and all postcode districts and sectors each add a number (when you get down to units start re-appearing again my own postcode for instance is TN (area) 6 (district) 2 (sector) and DN (unit)
I can subtotal them one by one within a filter (e.g. by selecting filter using starts with AL) but this means doing 105 separate operations which is a pain
Is there a formula which allows me to generate subtotals for anything in column 2 which starts with a letter in column 1 but fixes the problem of there being both two and one letter postcode areas?
e.g. I just want subtotals for AL and B and BA etc in the example above.
Thanks,
