CODEWITHSUNDEEP
sortingexcelvba
Jhecht
Jhecht

Reputation: 4435

Sorting data by groups in Excel

So, I've looked around and tried to solve this on my own. This isn't an absolutely crucial question currently, I just want to know if it could be done.

So let's say I've got a list with some data that looks like

Date      Location 
01/24/14  H-12
01/25/14  BB-44
01/30/14  G-12
01/29/14  7A-55
01/28/14  NN-15
01/24/14  GG-47

What I want is to be able to sort the data by Location, but I don't want it to be the general way, otherwise I'll end up with 7A-55, BB-44, G-12, H-12, NN-15. I want the data to be sorted so that double letters and single letters are sorted together. E.G. it should be G-12, H-12, BB-44, NN-15, 7A-55 once everything has been sorted.

I've tried creating a custom list sort, but it doesn't work. the way intended. The custom list I tried was A-Z, AA-ZZ, 7A (items were listed out, but for saving space I wrote them like that).

Like I said, this isn't a particularly huge deal if it can't be done, it just would have made it a little easier.

Edit 1 Here is what I would like to be the output

Date      Location
01/30/12  G-12
01/24/14  H-12
01/25/14  BB-44
01/24/14  GG-47
01/28/14  NN-15
01/29/14  7A-55

Edit

All of these worked in the regards i wanted to, although if I had to choose a favorite it would be the base 36 number conversion one. That was some real out-of-the-box thinking and the math geek in me appreciated it. Thanks everyone!

Upvotes: 0

Views: 230

Answers (4)

KekuSemau
KekuSemau

Reputation: 6852

Well it works, but is a bit complex, so rather just for fun: This UDF returns a value that can be used as sort key. It transforms the code into a four-digit base 36-number, i.e. using A-Z and 0-9 as symbols (like hex uses 0-9 and A-F). To get at your desired output, I literally put the symbols in this order, letters first (so "A" = 0 and "0" = 26). (The missing 'digits' are filled up with zeros, which are in this case "A"s)
It works ;)

Public Function Base36Transform(r As Range) As Long
    Dim s As String, c As String
    Dim v
    Dim i As Integer
    Dim rv As Long

    v = Split(r.Text, "-")
    s1 = v(0)
    s2 = v(1)
    s = Right("A" & s1, 2) & Right("A" & s2, 2)
    rv = 0
    For i = 1 To Len(s)
        c = Mid(s, Len(s) - i + 1, 1)
        If c Like "#" Then
            rv = rv + (Val(c) + 26) * (36 ^ (i - 1))
        Else
            ' c is like "[A-Z]"
            rv = rv + (Asc(c) - Asc("A")) * (36 ^ (i - 1))
        End If
    Next
    Base36Transform = rv
End Function

enter image description here

Upvotes: 1

pnuts
pnuts

Reputation: 59495

Assuming your data is in columns B:C with labels in Row1 and no intervening blank rows, add a column with: 

=IF(ISNUMBER(VALUE(LEFT(C2))),3,IF(FIND("-",C2)>2,2,1))

in D1 copied down to suit and sort ascending Location within sort ascending of the added column.

Upvotes: 1

Alex
Alex

Reputation: 1642

I am a little lazy here and assuming your data sits in Column A,B. You mightneed to adjust your range or the starting point of your list. But here's the code:

Sub sortttttt()
Dim rng As Range
Dim i As Integer
Range("B2").Activate
Do While Not IsEmpty(ActiveCell)
ActiveCell.Value = Len(ActiveCell.Value) & ActiveCell.Value
ActiveCell.Offset(1, 0).Activate
Loop

Set rng = Range("A1:B6")
rng.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes

Range("B2").Activate
Do While Not IsEmpty(ActiveCell)
ActiveCell.Value = Right(ActiveCell.Value, Len(ActiveCell.Value) - 1)
ActiveCell.Offset(1, 0).Activate
Loop

End Sub

Upvotes: 1

WGS
WGS

Reputation: 14179

Sorting is often a very creative process. VBA can ease up the process, but a little extension of the data will work just as well.

See my results:

enter image description here

The way I did it is by getting the length of each string, just to be safe. This is gotten by simply going =LEN(B2), dragged down.

Then I check if it starts with 7. If it does, assign 1, otherwise keep at 0. I used this formula: =(LEFT(B2,1)="7")*1, dragged down.

Now, my custom sort is this:

enter image description here

Now I might have gotten some things wrong here, or I might even have done overkill by going the Length column. However, the logic is pretty much what you're aiming for.

Hope this helps in a way! Let us know. :)

Upvotes: 1

Related Questions