Resorting document using VBA

[athard]

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.

[austenr]

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.

[Kenneth Hobs]

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

[Aussiebear]

Thanks Kenneth. Despite numerous examples of cross posting and supplying links, it seems the message doesn't get through to people.