Sorting Info

[BenS]

Does anyone know of a way to take information that is in a label format
per se (see below) and sort it first by State, then by City? Someone
told me that if I imported it into Excell, which I did, the info could
then be sorted but they don't know how and I've been wracking my brain
trying to figure it out. I have aproximately 1,000 names that I need
sorted first alpha by State then alpha by City, then alpha by name.
The info is currently laid out in Excell in a single column as
follows:

Name
Street Address
City, State Zip
Phone number

Name
Street Address
City, State Zip
Phone number

Etc.

Etc.

Thanks much!

 

[Max]

Try this:

Assume your data is in col A, A2 down, viz.:

<Col Label>
Name1
Street Address1
City1, State Zip1
Phone number1
<blank row>
Name2
Street Address2
City2, State Zip2
Phone number2
<blank row>
etc

Steps
--------
1. Remove all in-between blank rows
-------------------
Select col A

Press F5 > Special > Check "Blanks" > OK
(this will select all the blank cells in col A)

Right-click (on any selected part) > Delete > Check "Entire Rows" > OK

The above will delete all the blank rows in col A, resulting in:

<Col Label>
Name1
Street Address1
City1, State Zip1
Phone number1
Name2
Street Address2
City2, State Zip2
Phone number2
etc
---------------------

With the data starting in *row2* down (i.e. in A2 down)

2. Put in B2: =INDIRECT("A"&4*ROW()-6+COLUMN()-2)

3. Copy B2 across to E2, then copy down until zeros appear

The "column-wise" data in col A will be re-arranged "row-wise" into B2:E2,
viz.:

Name1...Street Address1...City1, State Zip1...Phone number1
Name2...Street Address2...City2, State Zip2...Phone number2
etc

3. To parse col D ("City1, State Zip1") into 2 parts, put:

In F2: =TRIM(LEFT(D2,SEARCH(",",D2)-1))
(this strips out the "City1" bit)

In G2: =TRIM(RIGHT(D2,LEN(D2)-SEARCH(",",D2)))
(this strips out the "State Zip1" bit)

Select F2:G2, copy down until the last line of data

4. Either freeze the values in cols B to G "in-place"
(or copy > paste special > values to another sheet)
-------------------------------------------------------------
Select cols B to F
Right-click > Copy
Right-click > Paste Special > Check "Values" > OK

5. Now you can sort by selecting the frozen values
and clicking Data > Sort

Sort by col G, then by col F > then by col B > OK

(your choice of ascending / descending)

 

[Max]

Oops, a typo correction, sorry ..

Line

Select cols B to F
in

4. Either freeze the values in cols B to G "in-place"
(or copy > paste special > values to another sheet)

should read