Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
The ascend sort return my list as:
EXCEL SORT WOULD PREFER THIS R10-1 R1-1 R10-10L R1-2 R10-2 R10-1 R1-1 R10-2 R11-1 R10-10L R1-2 R11-1 R12-1 R12-1 I would prefer the sort from the second column. The second column was assembled manually. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
I don't think you can change the sorting options, but if you insert your
information like this it will sort the way you want. R01-01 R01-02 R10-01 R10-02 R10-10L R11-01 R12-01 Another option would be to insert the information as time and use a custom format of R[h]-m but you would have to change the one with the L to a custom format of R[h]-mL Then it would look and sort the way you want. R1-1 - Inserted as 1:01 R1-2 - Inserted as 1:02 R10-1 - Inserted as 10:01 R10-2 - Inserted as 10:02 R10-10L - Inserted as 10:10 with secondary format type R11-1 - Inserted as 11:01 R12-1 - Inserted as 12:01 "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R10-1 R1-1 R10-10L R1-2 R10-2 R10-1 R1-1 R10-2 R11-1 R10-10L R1-2 R11-1 R12-1 R12-1 I would prefer the sort from the second column. The second column was assembled manually. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
On Mon, 5 Dec 2005 08:30:04 -0800, "Jose"
wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R10-1 R1-1 R10-10L R1-2 R10-2 R10-1 R1-1 R10-2 R11-1 R10-10L R1-2 R11-1 R12-1 R12-1 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "-", and then by the numbers that are after the "-". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =--MID(F1,2,FIND("-",F1)-2) H1: =LOOKUP(9.99E+300,--MID(F1,FIND("-",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. --ron |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
Thanks Ron, your recommendation was right on key. The issue was sorting
across the hyphen with what I had. Thanks all. -- Regards "Ron Rosenfeld" wrote: On Mon, 5 Dec 2005 08:30:04 -0800, "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R10-1 R1-1 R10-10L R1-2 R10-2 R10-1 R1-1 R10-2 R11-1 R10-10L R1-2 R11-1 R12-1 R12-1 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "-", and then by the numbers that are after the "-". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =--MID(F1,2,FIND("-",F1)-2) H1: =LOOKUP(9.99E+300,--MID(F1,FIND("-",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. --ron |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
Thanks Ron.
-- Regards "Ron Rosenfeld" wrote: On Mon, 5 Dec 2005 08:30:04 -0800, "Jose" wrote: The ascend sort return my list as: EXCEL SORT WOULD PREFER THIS R10-1 R1-1 R10-10L R1-2 R10-2 R10-1 R1-1 R10-2 R11-1 R10-10L R1-2 R11-1 R12-1 R12-1 I would prefer the sort from the second column. The second column was assembled manually. If I understand you correctly, you want to sort numerically first by the numbers that are between the "R" and the "-", and then by the numbers that are after the "-". The latter numbers may, on occasion, be followed by a letter. I would set up two "helper columns" in which you extract the sort keys. If your data is in Column F, then G1: =--MID(F1,2,FIND("-",F1)-2) H1: =LOOKUP(9.99E+300,--MID(F1,FIND("-",F1)+1,ROW(INDIRECT("1:5")))) Select the entire range to be sorted, in this case F1:H7, and select Data/Sort and do an ascending sort first on column G and then on Column H. --ron |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Alpha-Numeric Sorting
On Mon, 5 Dec 2005 12:59:02 -0800, "Jose" wrote:
Thanks Ron, your recommendation was right on key. The issue was sorting across the hyphen with what I had. Thanks all. -- Regards Glad it worked for you. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP WITH ALPHA NUMERIC | Excel Worksheet Functions | |||
Columns are now numeric, not alpha. how to change back? | Excel Discussion (Misc queries) | |||
Search string for alpha or numeric | Excel Worksheet Functions | |||
The colums changed from alpha to numeric how do you make it alpha | Excel Discussion (Misc queries) | |||
Alpha & Numeric Counts in Excel | Excel Discussion (Misc queries) |