Consulting

Results 1 to 4 of 4

Thread: Resorting document using VBA

  1. #1
    VBAX Newbie
    Joined
    Nov 2010
    Posts
    3
    Location

    Resorting document using VBA

    Hi,

    One of my clients gave me a zip code file. For some reason, it was sorted really badly and looks like this: (I have attached a sample.xls file too)


    Dealership City State Phone ZIPs
    Store 1 Omaha Ne. 555-555-5555
    23301 23418 23404 23441 23480
    23410 23357 23401 23417 23421
    23420 23422 23389 23308 23423
    23412 23414 23358 23341 23306
    23407 23486 23409 23345 23302
    23482 23359 23442 23350 23399
    23413 23427 23426 23337 23303
    23398 23408 23396 23354 23307
    23488 23416 23483 23415 23405
    23395 21817 23440 21838 21857
    23336 23356 23347 21866 21871
    21864 21851
    Store 2 Richmond Va. 555-444-6666
    23111 23162 23075 23116 23150
    23223 23141 23106 23250 23255
    23222 23227 23298 23069 23219
    23289 23232 23242 23220 23218
    23241 23249 23260 23261 23269
    23273 23274 23276 23278 23279
    23282 23284 23285 23286 23290
    23291 23292 23293 23295 23231
    23086 23124 23230 23228 23221
    23009 23058 23224 23226 23225
    Store 3 Los Angeles CA 555-666-777
    23289 23232 23242 23220 23218
    23241 23249 23260 23261 23269
    23273 23274 23276 23278 23279
    23282 23284 23285 23286 23290

    What I need is for all the zip codes to show up in the same row as the store name. So for example, Store 3 has 4 subsequent rows of zip codes. I need them all in the same row as Store 3.

    The file has about 2000 stores. I started to copy and paste and after 3 hours, I was just done with about 100 stores. Is there a short way to do this? I would really appreciate your help and advise.

    Thank you.

  2. #2
    VBAX Master austenr's Avatar
    Joined
    Sep 2004
    Location
    Maine
    Posts
    2,033
    Location
    Not an easy task since there is no static rows between stores. Meaning that the number of lines of zip codes can vary.

    What you really need is to read the zip codes into an array and then populate the cells using some kind of offset.

    Dont have time to work something up at the moment but someone with that experience will probably give you what you want in short order.
    Peace of mind is found in some of the strangest places.

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    This was solved by Wigi at ozgrid. It was also posted at one or two other threads like Mr. Excel. http://www.mrexcel.com/forum/showthread.php?t=509298

  4. #4
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,078
    Location
    Thanks Kenneth. Despite numerous examples of cross posting and supplying links, it seems the message doesn't get through to people.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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