Postcode counting

[Michael]

I have a large mailing (23,000 pieces) and I have to know how many people I
am mailing to in a particular post code zone.

For example I have postcodes with a prefix ranging from 2 to 4 characters
followed by a further 3 characters (a standard UK postal format)

L1 6TY
L14 8GR
CH42 7TR

I need to count the number of occurrences of L1 (remembering that L14 also
may get counted which should not happen), Then I would need a count for all
L14 and CH42 and all the other Post codes.

can anyone please help

thank you in advance

Michael

 

[Sheeloo]

If you have list of your codes in Col A and list of prefixes (2-4 characters)
in Col B
then enter this in C1

=SUM(IF(LEFT(A:A,FIND(" ",$B1))=LEFT($B1,FIND(" ",$B1)),1,0))

and press CTRL-SHIFT-ENTER
and copy down

This is based on the assumption that prefix is followed by a space and then
remaining three letters...

 

[Max]

A pivot on a helper col would be one quick, effective way to get there

Assume your data as posted is in A2 down
In B2: =LEFT(A2,SEARCH(" ",A2)-1)
Copy down to extract the necessary postcodes. Put a label into B1, say:
PCode, then select col B, and create a pivot. In Layout, drag n drop PCode
into both the ROW and the DATA areas (set as COUNT). That's it. You'd get the
full summary count for all postcodes in the pivot sheet. In a matter of
seconds.

Success? Click YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Pete_UK]

Assuming your postcodes are in column G, then you could put this
formula in an adjacent column (eg H2):

=LEFT(G2,FIND(" ",G2))

and copy down. This will give you the first part of the postcode - up
to and including the space.

Then with a separate list of those partial postcodes (including the
space), say in column P, you could have this formula in column Q:

=COUNTIF(H:H,P2)

and then copy this down. I've assumed that row 1 is used for headers.

You can use Advanced Filter to obtain a unique list of partial
postcodes.

Hope this helps.

Pete

 

[Matt Richardson]

A pivot on a helper col would be one quick, effective way to get there

Assume your data as posted is in A2 down
In B2: =LEFT(A2,SEARCH(" ",A2)-1)
Copy down to extract the necessary postcodes. Put a label into B1, say:
PCode, then select col B, and create a pivot. In Layout, drag n drop PCode
into both the ROW and the DATA areas (set as COUNT). That's it. You'd get the
full summary count for all postcodes in the pivot sheet. In a matter of
seconds.

Success? Click YES below.
--
Max
Singaporehttp://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---








- Show quoted text -

The easiest way to do this would be to use COUNTIF. An example
below:-

=COUNTIF(A1:A10,"L1 *")

Putting the space after the L1 would ensure that only L1 followed by
that space is allowed. If you are referring to another cell which
contains the code you're after (eg cell B1 might contain the text
'L1') you can create a function like this and it will do the same
job:-

=COUNTIF(A1:A10, B1&" *")

Again making sure you put in a space before the * character (which, in
case you're wondering acts like a wildcard character so will search
for anything that begins with your specified text). Simple enough.

Hope this helps,
Matthew Richardson
http://teachr.blogspot.com