Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Summarise Post Codes (Excel 2002/2003)

    Hi

    I need to be able to summarise by salesman so I can show the range by salesman.

    For example I need to create the contents of A3 automaticly, as you can column C goes from AB10 to AB16 then to AB21.

    I can do this small example manually but the master has much more data hence the reason I would like to automate it.

    Please see attached

    Any Ideas Please

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    Does the table in columns V through AD have anything to do with this?

    Will all post codes begin with AB? If not, could you provide a more representative example?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Summarise Post Codes (Excel 2002/2003)

    Hi Hans

    V through AD have no bearing on this, I have removed them from the file. There is also a bit more data in there.

    As you can see A3 is entered manually I would like to automate it.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    I notice that for example for Carlisle, the order is

    CA1
    CA10
    ...
    CA19
    CA2
    CA20
    ...
    CA29
    CA3
    ...

    In other words, the sort order is alphabetic, not numeric. Is this correct, or should CA1, CA2, CA3 etc. be grouped together at the beginning?

    Also (remember, I'm not a UK citizen), do ALL codes begin with two letters - never one, never three or more?

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Summarise Post Codes (Excel 2002/2003)

    Hi Hans

    The Code sequence can be any combination of one alpha and one numeric to two alpha and two numeric. ie B1, AB1, AB12 etc

    Hope this helps

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    And what about the sort order?

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Summarise Post Codes (Excel 2002/2003)

    Hi Hans

    Sort would be by Post Dist column B.

    Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    Braddy, please try to provide exact and correct information. If I sort the worksheet on column B, the cities will be jumbled. I cannot imagine you would want that.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Summarise Post Codes (Excel 2002/2003)

    Hi Hans

    Sorry to waste your precious time, I think I will have to give up on this one.

    Many Thanks for your patience.

    Braddy
    If you are a fool at forty, you will always be a fool

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    There is no reason to give up, we'll solve it. To get back to my original question: is the sort order for Carlisle in your workbook correct, or should it have been

    CA1
    CA2
    ...
    CA9
    CA10
    CA11
    ...

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    Oh, there's more:

    The Channel Islands don't conform to the pattern - only two letters IC. I suppose this is a group on its own

    London has letters *after* the digits, for example EC2N. How should these be grouped?

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    Is the issue sorting based on the codes or filling out column A or both? I am a littel confused about why some the divisions in A were chosen: could you elaborate?

    Steve

  13. #13
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Summarise Post Codes (Excel 2002/2003)

    Hi Hans



    Your sort for Carlisle is correct, but I don't know how to do that.

    I have attached a txt file to try explain better what I am trying to do. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    I first did a sort on Salesman, that shows me all the PostCodes he is responsible for but as you suggest they are not sorted correctly.

    Many Thanks

    Braddy
    If you are a fool at forty, you will always be a fool

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    On relatively simple way would be to create column and use the formula:
    =SUBSTITUTE(C3,B3,TEXT(B3,"00"))

    Copy it down the column and sort on this column. I presume that "post District" is the number and it is always 2 digits. if it can be 3 use:
    =SUBSTITUTE(C3,B3,TEXT(B3,"000"))

    Steve

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summarise Post Codes (Excel 2002/2003)

    What should be the primary (most important) sort order? Salesperson or post code?

Page 1 of 2 12 LastLast

Posting Permissions

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