sorting

[Chris Hoagland]

i need to sort a column for addresses. i can get it to sort somewhat.

example

n 7 ave e
n 7 ave w
s 8 ave e
s 8 ave w
w 7 st s
e 7 st s
w 8 st n

i would like to sort them by n w s e
it puts them in n s but alphabetizes the w e ones

any help?

 

[JMB]

Go to Tools/Options/Custom Lists. Create a new list N, W, S, E.

Then insert a column to the left of your data. Use Left(b1,1) (Im assuming
your data is now in cell B1 since we inserted a column- change if you need
to) to create a cheater column. Copy this formula down the length of your
table.

then select the cheater column you created and the rest of your table. Goto
Data/Sort. First sort key should be your cheater column. Hit options
button. For first key sort order, select the custom list you created.

Hope this helps.

 

[JMB]

Unfortunately, the custom options only work for the primary key.

If you wanted to then sort the list using the last character as a secondary
key, you could enter this formula in your cheater column, Right(B1,1). This
time, you would have to sort the table 4 times. Essentially, once you have it
sorted using the first character as a key (N,W,S,E), you would select the N's
section of the table (and your cheater column), sort the N's using the method
in my last post, and so on for the W's, S's and E's.

 

[Max]

Just another play to try ..

Assuming the sample list below is in A2:A8

n 7 ave e
n 7 ave w
s 8 ave e
s 8 ave w
w 7 st s
e 7 st s
w 8 st n

Put in B2: =MATCH(LEFT(TRIM(A2),1),{"n";"w";"s";"e"},0)
Copy down to B8

Then select A2:B8 and sort by col B, ascending

And if there's the possibility of unmatched data in col A,
use instead in B2:

=IF(ISNA(MATCH(LEFT(TRIM(A2),1),{"n";"w";"s";"e"},0)),"",MATCH(LEFT(TRIM(A2)
,1),{"n";"w";"s";"e"},0))

Then select & sort by col B as before
Unmatched cases (if any) will be sorted below "e"

 

[David McRitchie]

I think you may have an additional problem with spaces or other characters
in front of the initial direction N,S,E,W. See TrimALL macro at
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
actually Max's solution covers that if it is spaces, and
not CHAR(160).