Excel sorting function question
Hello,
When sorting a column of cells (with the sort order set to "A to Z") in Excel, the following values would be sorted thus:
C-121
C-122
C-123
C-124
But suppose you wanted the search function to interpret the hyphens as minus signs so that sorting them (again with the sort order set to "A to Z") would produce this:
C-124
C-123
C-122
C-121
How could that be done?
I tried setting it up in a number of different ways as shown below but nothing would sort in the order described, i.e. with the exception of sorting by column E, it always treated the minus sign as a hyphen and sorted in the order shown.
(The formulas in cells B11:E11 are shown below in blue and those in cells G8:L8 are shown above in red. Column B is only present for scrambling.)
Thanks
When sorting a column of cells (with the sort order set to "A to Z") in Excel, the following values would be sorted thus:
C-121
C-122
C-123
C-124
But suppose you wanted the search function to interpret the hyphens as minus signs so that sorting them (again with the sort order set to "A to Z") would produce this:
C-124
C-123
C-122
C-121
How could that be done?
I tried setting it up in a number of different ways as shown below but nothing would sort in the order described, i.e. with the exception of sorting by column E, it always treated the minus sign as a hyphen and sorted in the order shown.
(The formulas in cells B11:E11 are shown below in blue and those in cells G8:L8 are shown above in red. Column B is only present for scrambling.)Thanks
Rory Archibald
I think you would have to use two columns, since it will always treat those as pure text.
Why not just sort Descending (Z to A)?
ASKER
Thank you for the responses.
>>>use two columns
>>>sort Descending (Z to A)
If either of those two solutions were an option, I would not be asking the question.
As a simplified example of what I am trying to do, suppose you have a single column in a spreadsheet which contains values representing a range of dates (say the 7th to the 9th of some month) and a series of events (say we call them events 1 thru 3) occurring in each of the days. Furthermore, suppose you want the capability to sort the column in a recent-to-oldest order by date but first-to-last order by events in those days:
Day-event#
9-1
9-2
9-3
8-1
8-2
8-3
7-1
7-2
7-3
Obviously, that would be easy to do by using Text-to-Column to parse the data (with the hyphen as the delimiter) followed by a two column sort in which the first column (date) sort order is Z to A and second column (event number) sort order is A to Z.
However, suppose that using a two column approach is not an option. Is there any possible way to configure or format the entries in a single column so that the sort process will produce the results shown? That is my question.
>>>use two columns
>>>sort Descending (Z to A)
If either of those two solutions were an option, I would not be asking the question.
As a simplified example of what I am trying to do, suppose you have a single column in a spreadsheet which contains values representing a range of dates (say the 7th to the 9th of some month) and a series of events (say we call them events 1 thru 3) occurring in each of the days. Furthermore, suppose you want the capability to sort the column in a recent-to-oldest order by date but first-to-last order by events in those days:
Day-event#
9-1
9-2
9-3
8-1
8-2
8-3
7-1
7-2
7-3
Obviously, that would be easy to do by using Text-to-Column to parse the data (with the hyphen as the delimiter) followed by a two column sort in which the first column (date) sort order is Z to A and second column (event number) sort order is A to Z.
However, suppose that using a two column approach is not an option. Is there any possible way to configure or format the entries in a single column so that the sort process will produce the results shown? That is my question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Actually dlmille I don't think the asker is using dates. I believe he just used them for clarification.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks