Sorting by PostCode

I work for a local UK council, and am trying to record responses to a
government campaign.

My town is divided up into 16 "wards" and a total of 3300 postcodes,
formatted like "BH11 8HU". I have a list in alphanumeric order of these
postcodes with their ward recorded as well.

On a seperate sheet I have a list of addresses, including postcodes, of
respondents. How would I best go about automating a search down the
respondents and counting them against their ward?

Thanks in advance.

Steve

Hi Steve

the only thing that occurs to me is to count the number of responents with a
specific postcode (using COUNTIF) and then use the subtotal function to add
up the number per ward (or use a Pivot table for this)

assume your respondant's addresses and postcodes are in sheet2 range
A1:C1000 where column C contains their postcodes
assume your list of wards and postcodes are in sheet1 range A1:B3300 where
column A is the Ward and column B is the postcode
on both sheets row 1 contains the column headings
in C1 type Respondents (or similar)
in cell C2 type
=COUNTIF(Sheet2!$C$2:$C$1000,B2)
move the mouse over the bottom right hand corner of the cell, when you see a
+ double click, this will fill the formula down the column giving you a
count of the number of respondents in each postcode.
Now, ensure that this sheet is sorted in Ward order
Click in any cell in the data range and choose
Data / Subtotals
choose at any change in WARD
SUM
and tick Respondents as the field to add up
click OK
and you'll see a sub-total at the bottom of each ward
on the left of the screen you'll see little numbers, clicking on the 1 will
give you the total number of responders, clicking on the 2 will give you the
number of responders per ward.

an alternative to the subtotal option is to create a pivot table - click in
your data range (on sheet1) choose data / pivot table and pivot chart
report, click Next, check the range, click Next, choose new worksheet, click
Next.

Now drag the ward field to where it says "drop row fields here"
drag the Respondents (or whatever the title in Sheet1!C1 is) to where it
says Data
and you'll get your answer

Hope this helps
Cheers
JulieD


"Steve" <[email protected]> wrote in message
news:[email protected]...
>I work for a local UK council, and am trying to record responses to a
> government campaign.
>
> My town is divided up into 16 "wards" and a total of 3300 postcodes,
> formatted like "BH11 8HU". I have a list in alphanumeric order of these
> postcodes with their ward recorded as well.
>
> On a seperate sheet I have a list of addresses, including postcodes, of
> respondents. How would I best go about automating a search down the
> respondents and counting them against their ward?
>
> Thanks in advance.
>
> Steve
>

Hi!

You might consider this approach.

You have respondents and post codes in one list. This list would be
more complete and usable if you also had their ward alongside.

Do this using vlookup(<postcode>,<array containing postcodes and
Wards>,2,false). If you need more help on vlookup, come back.

Sort this 3 column (person postcode,ward) array by ward.
Now apply Data > Subtotals or use Data > Filter > Autofilter to tease
out what you need.
(NB: given our crazy UK postcodes, probably get rid of any blanks they
hold, otherwise lookup could get difficult, and hence subtotals could
show some nonsenses.)

Alf

Alf

Thanks for replying. I had already used Julie's method, but had moved
on to pre-empting a request for feedback by scheme (we have 3 different
schemes operating at the moment), by ward. Your method will help me
deal with that. Thank you.

Steve

Julie

Thanks very much for this; it worked a treat.

Steve

you're welcome and thanks for the feedback

"Steve" <[email protected]> wrote in message
news:[email protected]...
> Julie
>
> Thanks very much for this; it worked a treat.
>
> Steve
>

Julie

Thanks very much for that; worked a treat.

Steve