Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Jose
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Sloth
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Jose
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Jose
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLOOKUP WITH ALPHA NUMERIC JACOB Excel Worksheet Functions 14 June 16th 05 04:10 PM
Columns are now numeric, not alpha. how to change back? samriepe Excel Discussion (Misc queries) 1 June 9th 05 03:17 PM
Search string for alpha or numeric David Excel Worksheet Functions 4 June 8th 05 05:19 PM
The colums changed from alpha to numeric how do you make it alpha worldmade Excel Discussion (Misc queries) 2 May 26th 05 03:44 PM
Alpha & Numeric Counts in Excel Programmer wanna be Excel Discussion (Misc queries) 3 April 5th 05 11:12 AM


All times are GMT +1. The time now is 03:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"