1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Convert Excel zip list to simplified list

Discussion in 'Business Applications' started by six2pelham, May 5, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. six2pelham

    six2pelham Thread Starter

    Joined:
    May 5, 2010
    Messages:
    3
    I've been banging my head on the desk trying to figure out how to simplify my zip list. Can anyone help with the attached example. I'm thinking I need VB code to do this but haven't been successful finding a solution.

    thanks,
    Jeff
     

    Attached Files:

  2. Sponsor

  3. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,346
    I hope this can help you stop banging your head :)

    I coded a macro for you.
    It's not full of explanations and all it does is excecute what you need doen step by step.

    The first step is sort columns AB for STATE and ZIP to keep these together.

    Then it just goes down the line and carries out what you vant,
    If the zips are consecutive a dash will be set,
    If ther are not, a comma is set

    It will work in any version of Excel
     

    Attached Files:

  4. six2pelham

    six2pelham Thread Starter

    Joined:
    May 5, 2010
    Messages:
    3
    Wow !! Awesome!

    For some reason I get a comma and dash in some places or 2 commas. Added revised sheet with 3-digit zips to show what I mean.
     

    Attached Files:

  5. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,346
    Assumptions kill the best projects!

    Okay, I didn't take into account the fact that you would have duplicate zip for the same state, that was not in your example, an extra line of code to test this be added to skip these.
    I'll see if i can do it but it will later this evening.
     
  6. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,346
    Hi Jeff,

    I think I got it working, I rewrote the code and simplified it some.
    As far as I tested, I dind't really do that much it loks like it works, MD (Madison) was the test where 213 is missing.
     

    Attached Files:

  7. six2pelham

    six2pelham Thread Starter

    Joined:
    May 5, 2010
    Messages:
    3
    Awesome!!!! It works perfectly. Thank you very much!!!!

    Head banging has ceased!!:)
     
  8. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,346
    If you're interested in understanding the thought behind the coding let me know.
    Glad to have helped.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921337