Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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" wrote in message ups.com... 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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
Julie
Thanks very much for this; it worked a treat. Steve |
#5
|
|||
|
|||
you're welcome and thanks for the feedback
"Steve" wrote in message oups.com... Julie Thanks very much for this; it worked a treat. Steve |
#6
|
|||
|
|||
Julie
Thanks very much for that; worked a treat. Steve |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel | |||
Sorting Spreadsheet with Merged Fields | Excel Discussion (Misc queries) | |||
sorting question | Excel Discussion (Misc queries) |