Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel sorting function question

Posted on 2012-03-27
Medium Priority
279 Views
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
0
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2
• 2
• +1

LVL 85

Expert Comment

ID: 37773787
I think you would have to use two columns, since it will always treat those as pure text.
0

LVL 49

Expert Comment

ID: 37773793
Why not just sort Descending (Z to A)?
0

Author Comment

ID: 37774530
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.
0

LVL 49

Accepted Solution

Martin Liss earned 668 total points
ID: 37774686
If either of those two solutions were an option, I would not be asking the question.

Sorry about that but from here I can't tell. In any case you can do what you want via VBA code.

``````Option Explicit

Private Sub CommandButton1_Click()
Dim r As Range
Dim i As Long
Dim strData() As String

Set r = Range("A1").End(xlDown).Offset(0, 0)
ReDim strData(1 To r.Row)

For i = 1 To r.Row
strData(i) = Range("A" & i)
Next

BubbleSortDec strData

For i = 1 To r.Row
Range("A" & i).Value = strData(i)
Next

End Sub
Public Sub BubbleSortDec(ByRef pvarArray As Variant)
Dim i As Long
Dim iMin As Long
Dim iMax As Long
Dim varSwap As Variant
Dim blnSwapped As Boolean
Dim strParts1() As String
Dim strParts2() As String

iMin = LBound(pvarArray)
iMax = UBound(pvarArray) - 1
Do
blnSwapped = False
For i = iMin To iMax
strParts1 = Split(pvarArray(i), "-")
strParts2 = Split(pvarArray(i + 1), "-")
If strParts1(0) < strParts2(0) Then
varSwap = pvarArray(i)
pvarArray(i) = pvarArray(i + 1)
pvarArray(i + 1) = varSwap
blnSwapped = True
End If
If strParts1(0) = strParts2(0) Then
If strParts1(1) > strParts2(1) Then
varSwap = pvarArray(i)
pvarArray(i) = pvarArray(i + 1)
pvarArray(i + 1) = varSwap
blnSwapped = True
End If
End If

Next
iMax = iMax - 1
Loop Until Not blnSwapped
End Sub
``````
0

LVL 42

Assisted Solution

dlmille earned 668 total points
ID: 37774894
For interest only, as I don't think this is a valid solution for every day in the month for below stated reasons:

For the Day/Event you could code as MONTH-DAY and custom format M-D.

However, you'd max out at 12 for the Day (12 months max in a year) and 28 events (at least for Day 2/Event 28).

Within those constraints, you could sort the way you're describing.

Dave
0

LVL 49

Expert Comment

ID: 37774934
Actually dlmille I don't think the asker is using dates. I believe he just used them for clarification.
0

LVL 85

Assisted Solution

Rory Archibald earned 664 total points
ID: 37775278
Short answer: no, I don't believe there is anything you could do to one column of data to make the built in sort function do what you want.

As an aside, if there are constraints on the solution, it helps to state those up front. ;)
0

Author Closing Comment

ID: 37876539
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,â€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
###### Suggested Courses
Course of the Month8 days, 21 hours left to enroll