+ Reply to Thread
Results 1 to 6 of 6

Counting postcodes

  1. #1
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79

    Smile Counting postcodes

    I'm working on a summary of premium postcode deliveries, whereby I have a sheet with raw data, and a summary sheet which tells me how many times the postcode appears.

    The problem is that sometimes we're interested in a different number of digits of the postcode for instance:

    IV (all IV postcodes)
    or
    IV6 (Only IV6 not any other IV codes)
    or
    IV6 1 (only IV6 1 codes not any other IV6 codes)

    I have about 50 postcodes entries (i.e. IV, ED6, TR12 4) in total to summarize. Is there a quick way to count the number of occurrences of each from the list?

    I bodged up a way that works yesterday, but the postcode data is 64000 rows long, and my method effectively mirrored this 5 times (and crashes my machine every 5 mins). I'll post an example of how I bodged if no-one has any simpler ideas (otherwise I'd rather not embarrass myself lol).

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Maybe you could use a Pivot Table? This will tell you how many of each postcodes you have

  3. #3
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    I've tried playing with pivot tables but it claims I have too much data to fit, or something similar.

    Can you use a countif with a 'contains' text element? At the moment, I'm using an andvanced filter and countif using the LEN function, but its using way too much calculation power.

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    You can also use Data Subtotals if your potscode column is sorted

  5. #5
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    Quote Originally Posted by mikeyfear
    Can you use a countif with a 'contains' text element?
    COUNTIF accepts wildcards, so to count all postcodes that start with "IV"

    =COUNTIF(range,"IV*")

  6. #6
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    Blonde moment... I spent ages yesterday trying to do just that, but I couldn't add the wildcard to a cell value so I gave up. I only just thought I can just add a CountIF(B:B, A1 & "*") .

    Thanks tho, that's just what I was after. My 48mb file is now 300k (oopsy).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1