Thread: Sorting Issue

Hello,

I have been trying to solve an issue I have with how excel sorts my information so hopefully you gurus will be able to help me out. I have looked all over the web trying to find a solution and I can't seem to figure it out.

Attached is a spreadsheet that is a good example of what I am trying to accomplish. To give a little idea of why I need to sort in this way the numbers are part numbers in a warehouse and we are moving to a new warehouse so I need to be able to sort this list so that it is in the same order as the parts are in on my shelves so we now how to space the parts on the shelves.

If you go to row 56 you will see where there is an issue SBDS1464 is before SBDS737 where as on my shelf the order they are in is by the prefix first then by the root in ascending order so obviously 737 is way before 1464 and some of them also have a suffix and the way we handle those is for example on my shelf if we had SBDS737, SBDS737T and SBDS738 they would be in that order exactly. Then after we are all the way through SBDS prefix it goes to SBES and then by ascending order by the root number again and so on and so forth through all of the prefixes.

I am sorry if this doesn't make much sense if something is confusing let me know and I will do my best to clarify. Normally I would just do it manually as a normal sort gets it kind of close but for all of my lines the report will be thousands of lines long and it will take forever.

Thanks in advance the the help I appreciate it.

Kelly

You could use =LEFT(A1,4) and =RIGHT(A1,LEN(A1)-4) or just Text to Columns (fixed width) to split the code into two columns. Then sort on the "numeric part.

Would that give youwhat you want?

Regards, TMS

Also in my example all of the part numbers have a 4 letter prefix but in reality my prefixes vary in length.
Is there a way to if you have a string say abcd1235ef to tell excel to split that into abcd 1235 ef? The hard part is that the prefix root and suffix vary in length so it need to be able to split text number then text not a set number of characters.

You could use a UDF that expands the numeric part:

      ---A--- ---B---- ------------C-------------
  1    Input   Output                            
  2   SBDS737 SBDS0737 B2 and down: =PadNum(A2,4)
  3   SBDS749 SBDS0749                           
  4   SBDS794 SBDS0794                           
  5   SBDS796 SBDS0796                           
  6   SBDS804 SBDS0804

Then sort by the second column.

Function PadNum(sInp As String, Optional ByVal iLen As Long = 1) As String
    ' shg 2003-1115

    ' Expands numbers in a string to iLen characters for sorting; e.g.,
    '   PadNum("13A1U3", 2)    = "13A01A03"
    '   PadNum("1.2.3.15", 3)  = "001.002.003.015"

    ' Numbers are not shortened below their minimal representation:
    '   PadNum("1.123.2.3", 2) = "01.123.02.03"

    ' Returns unpadded values if iLen omitted
    '   PadNum("01.123.02.03") = "1.123.2.3"

    ' All non-numeric characters are returned as-is

    Dim sFmt        As String
    Dim iChr        As Long
    Dim sNum        As String
    Dim sChr        As String
    Dim bNum        As Boolean

    sFmt = String(IIf(iLen < 1, 1, IIf(iLen > 15, 15, iLen)), "0")

    For iChr = 1 To Len(sInp) + 1   ' the +1 flushes a trailing number
        sChr = Mid(sInp, iChr, 1)
        If sChr Like "#" Then
            bNum = True
            sNum = sNum & sChr
        Else
            If bNum Then
                bNum = False
                PadNum = PadNum & Format(CDbl(sNum), sFmt)
                sNum = vbNullString
            End If
            PadNum = PadNum & sChr
        End If
    Next iChr
End Function

In Excel, press Alt-F11 to open up the VB Editor
Select Insert | Module
Copy and paste the code for the User Defined Function into the newly created module.

Back in Excel, in a cell in Row 1, type =PadNum(A1,4) as shown above. Copy down.

Sort on the new column.


Regards, TMS

Change the 4 to 5: =PadNum(A1,5) ... or 6 even, if you might have six digit numbers


Regards

Well, the credit goes to shg ... it was, after all, his UDF. I just helped tweak the fine detail.

But, thanks for the rep.