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

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...

"Michael" wrote:

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

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
---
"Michael" wrote:
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.

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


On Feb 27, 12:13*am, Michael
wrote:
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