Alphanumeric Sorting [SOLVED]

[Bikram]

I need to sort alphanumeric data. The sorting has to be based on the numbers irrespective of the alphabets.


Current format and order:

2 = GHNB

1 = XGV

8 = ADKH

5 = MNJSDV


Required order

8 = ADKH

5 = MNJSDV

2 = GHNB

1 = XGV


Please note that I have 1000s of columns of data and need to sort all the columns. So, I need a macro that works on several columns of data.

 

[NARAYANK991]

Hi Bikram ,


Can you copy + paste many more rows of actual data ?


From the sample you have given , I cannot conclude anything ; are the values in one column , over 4 rows ? If so , then a normal Z to A sort seems to do the job.


Better still , upload a sample workbook , which is less than 1 MB in size.


Narayan

 

[SirJB7]

Hi, Bikram!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


You should use a sort of sort customized since your data doesn't respond to a normal Z to A descending (and neither to A to Z ascending) order schema. Here's a sample of the procedure, and as you were requested a sample file will help, but either tell us how many elements do you have for your criteria or include as much values as you can so as to choose the better method to define the custom list (one thing is 4 and other is 100 or 1000).


-----

[pre]

Option Explicit

Sub Macro1()
    Columns("B:B").Select
    Application.AddCustomList ListArray:=Array("ADKH", "MNJSDV", "GHNB", "XGV")
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Hoja1").Sort.SortFields.Add Key:=Range("B1:B4"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "ADKH,MNJSDV,GHNB,XGV", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Hoja1").Sort
        .SetRange Range("B1:B4")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B1").Select
End Sub

[/pre]
-----


Regards!


EDITED


PS: Now with your file I understood that the "N = XXXXXX" it's the whole content of cells, I interpreted it as an assignment of the equivalence for sorting order, please discard the previous code.

 

[Bikram]

Dear Narayan,


Thank you for your prompt response. Please find below the link to my sample data. Please note that the number of columns are more than 1000. I want the data to be sorted on the numbers. The cell having the highest number remains at the top.


https://www.dropbox.com/s/x94c0rcb89tpqcd/Sample%20Data.xls


I am aware of a code that works for a single column only. Please refer to the sheet2 of the attached workbook for the VBA code. The code works for col A by extracting digits from a cell and creating a helper column in a new worksheet. But I have no idea how to modify the code for it to work for 1000s of columns.


I look forward to hearing from you soon.

 

[NARAYANK991]

Hi Bikram ,


Thanks for uploading a sample file ; hope someone else will respond tonight , since it is late for me ; I can get back to you on this only tomorrow morning.


Narayan

 

[Bikram]

Dear SirJB7,


Thank you for your quick response. I appreciate it. Yes, my data has several thousands of columns and I need to sort on numbers only.


Bikram

 

[SirJB7]

Hi, Bikram!


Give a look at this file:

https://dl.dropboxusercontent.com/u... Sample Data (for Bikram at chandoo.org).xlsm


This is the code, which sorts by numbers in descending order and if equal by names in ascending order (to avoid this last option remove the "Or (iNumI = iNumJ And sStrI > sStrJ)" from the related If instruction:

-----

[pre]

Option Explicit

Sub SortAllColumns()
    ' constants
    Const ksWS = "Sheet3"
    ' declarations
    Dim I As Integer
    ' start
' remove this and next line, they're just for testing purposes
Worksheets("Sample").Range("A2:D13").Copy Worksheets("Sheet3").Range("A2")
    ' process
    With Worksheets(ksWS)
        For I = 1 To .Columns.Count
            If .Cells(1, I).Value = "" Then Exit For
            SortAColumn ksWS, I
        Next I
    End With
    ' end
    Beep
End Sub

Sub SortAColumn(psSheet As String, piColumn As Integer)
    ' constants
    Const ksSeparator = "="
    ' declarations
    Dim lLast As Long, iNumI As Integer, iNumJ As Integer, sStrI As String, sStrJ As String
    Dim I As Long, J As Long, K As Integer, A As String
    ' start
    ' process
    With Worksheets(psSheet).Columns(piColumn)
        ' last row
        If .Cells(.Rows.Count, 1).Value = "" Then
            lLast = .Cells(.Rows.Count, 1).End(xlUp).Row
        Else
            lLast = .Rows.Count
        End If
        ' sort
        For I = 2 To lLast - 1
            A = .Cells(I, 1).Value
            K = InStr(A, ksSeparator)
            iNumI = Val(Trim(Left(A, K - 1)))
            sStrI = Trim(Right(A, Len(A) - K))
            For J = I + 1 To lLast
                A = .Cells(J, 1).Value
                K = InStr(A, ksSeparator)
                iNumJ = Val(Trim(Left(A, K - 1)))
                sStrJ = Trim(Right(A, Len(A) - K))
                If iNumI < iNumJ Or (iNumI = iNumJ And sStrI > sStrJ) Then
                    ' swap
                    K = iNumI
                    iNumI = iNumJ
                    iNumJ = K
                    A = sStrI
                    sStrI = sStrJ
                    sStrJ = A
                    A = .Cells(I, 1).Value
                    .Cells(I, 1).Value = .Cells(J, 1).Value
                    .Cells(J, 1).Value = A
                End If
            Next J
        Next I
    End With
    ' end
End Sub

[/pre]
-----


Just advise if any issue.


Regards!

 

[Bikram]

Dear SirJB7,


Thank you very much for the code. It worked like a charm. I am a big fan of yours now!


Bikram

 

[SirJB7]

Hi, Bikram!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!