Excel help pretty please

Home Forum Chat Forum Excel help pretty please

Viewing 12 posts - 1 through 12 (of 12 total)
  • Excel help pretty please
  • scruff
    Member

    got a list of postcodes & need to count how many of each postcode group (ie WS13, WS 14) there are.

    In column B there is a list of post codes nearly 3000 long, like this

    WS13 8QR
    WS14 6RP
    WS14 9DG
    WS14 9EF
    WS15 1AE
    WS15 1BP
    etc

    I Want it to return how many WS13, WS14 etc there are, cant get =countif to work as I want.

    ???

    Premier Icon wwaswas
    Subscriber

    split the column on ‘ ‘ (space) – it’ll make life easier for you.

    then sort by prefix and split with a count.

    WackoAK
    Member

    you can create an additional column that creates the first part for you by using this formula =LEFT(#CEll#,LEN(#CELL#)-4)

    where #cell# is the postcode, you can then run a pivot table to get counts.

    The above formula will be able to take into account WS1 etc.

    edit: or use the simple method by wwaswas above, doh!

    Premier Icon thepurist
    Subscriber

    [EDIT]D’oh sorry, was suggesting Countif and then read the last line of the post…

    djglover
    Member

    Add a new column with =LEFT(A1,FIND(” “,A1)-1)

    Premier Icon Stoner
    Subscriber

    If you dont already have a list of unique postcode stems (WR13, WR14 etc) to do your counting against then you can extract them from your list using:

    http://www.cpearson.com/excel/ListFunctions.aspx

    and

    http://www.cpearson.com/excel/noblanks.htm

    acjim
    Member

    try this:
    Assuming your list of postcodes is in cells B1 to B3000

    List your codes that you want to count, the WS13 etc in C1 to say C50

    Now in cell D1 enter this function

    =COUNTIF($B$1:$B$3000,C1&"*")

    And copy it down next to all of your WS13 etcs

    The &”*” in the function is a text wild card so counts anything starting with the text you set as your criteria.

    scruff
    Member

    will try that, am I in for a fun morning or what …

    scruff
    Member

    acjim,it works but includes B11, B12 in the B1s.

    Premier Icon Stoner
    Subscriber

    as you say, acjim’s shortcut wont work.

    Once youve pulled out your “thin” list, then use the countif function to count incidences.

    acjim
    Member

    oh I see, sorry long and very dull day yesterday!

Viewing 12 posts - 1 through 12 (of 12 total)

The topic ‘Excel help pretty please’ is closed to new replies.