Solved

Excel sorting function question

Posted on 2012-03-27
8
270 Views
Last Modified: 2012-04-21
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.1(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
Comment
Question by:Steve_Brady
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
I think you would have to use two columns, since it will always treat those as pure text.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Why not just sort Descending (Z to A)?
0
 

Author Comment

by:Steve_Brady
Comment Utility
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 45

Accepted Solution

by:
Martin Liss earned 167 total points
Comment Utility
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

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 167 total points
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Actually dlmille I don't think the asker is using dates. I believe he just used them for clarification.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 166 total points
Comment Utility
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

by:Steve_Brady
Comment Utility
Thanks
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now