Sorting zip code file-Please Help

A

athard

New Member
Joined
Jun 24, 2009
Messages
18
Hi,

One of my clients gave me a zip code file. For some reason, it was sorted really badly and looks like this:


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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Is it a text file with single spaces between zip codes or some other chacter(s), or is it an Excel file?

If it's an Excel file, are the zip codes in separate cells in columns A to E or are they all in column A?

Is the store data all in column A or does it span several cells?

Which version of Excel are you using?

Are you familiar enough with VBA to know what to do with a VBA subroutine if someone wrote one for you or would you need walking through the process?
 
Upvote 0
Ruddles said:
Is it a text file with single spaces between zip codes or some other chacter(s), or is it an Excel file?

If it's an Excel file, are the zip codes in separate cells in columns A to E or are they all in column A?

Is the store data all in column A or does it span several cells?

Which version of Excel are you using?

Are you familiar enough with VBA to know what to do with a VBA subroutine if someone wrote one for you or would you need walking through the process?
Click to expand...

Ruddles, thanks for your interest in solving my problem. I will answer your questions one by one:

1. It is an Excel file.
2. The zip codes are in separate columns from A to E.
3. The Store data spans several cells. (A to E)
4. Excel 2007.
5. I am not very familiar with VBA. I know a bit. If you give me a basic walkthrough, I think I can manage. Plus, I work with a couple of people who know a bit of VBA, so between the 3 of us, we will manage.

Thanks again. I look forward to hearing from you.
 
Upvote 0
Do this in a copy of your workbook!

Right-click the tab of the sheet containing your data and select View Code. Paste this code over anything that's already in the code window. Return to Excel and go Developer > Macros, select ReformatZips and click Run.

Whatever you do, DO NOT BLINK! :)
Code: 
Option Explicit
 
Sub ReformatZips()
 
  Dim iLast As Long
  Dim iRow As Long
  Dim iStore As Long
  Dim iCol As Integer
  Dim iNewPos As Integer
 
  Application.ScreenUpdating = False
 
  iLast = Cells(Rows.Count, 1).End(xlUp).Row
  For iRow = 2 To iLast
    If Left(Cells(iRow, 1), 5) = "Store" Then
      iStore = iRow
    Else
      For iCol = 1 To 5
        If Not IsEmpty(Cells(iRow, iCol)) Then
          iNewPos = Cells(iStore, Columns.Count).End(xlToLeft).Column + 1
          Cells(iStore, iNewPos) = Cells(iRow, iCol)
        End If
      Next iCol
    End If
  Next iRow
 
  For iRow = iLast To 2 Step -1
    If Left(Cells(iRow, 1), 5) <> "Store" Then
      Rows(iRow).Delete Shift:=xlUp
    End If
  Next iRow
 
  Application.ScreenUpdating = True
 
End Sub
I think it's working okay but it's your responsibility to check the output against the input - pick a few random entries and check they appear in the output, particularly around the 'extremities' of each Store record and of the entire file because that's where these things normally go wrong if they're going to.

Report any problems asap!
 
Upvote 0
Oh great - I just wasted twenty minutes of my time! Who was it? Oh yes, Athard - I shall remember that name and ignore his posts in the future!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,216,098
Messages
6,128,812
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top