Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sorting
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? |
#2
|
|||
|
|||
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. "Chris Hoagland" wrote: 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? |
#3
|
|||
|
|||
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. "Chris Hoagland" wrote: 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? |
#4
|
|||
|
|||
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" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Chris Hoagland" wrote in message ... 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? |
#5
|
|||
|
|||
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). --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |