Excel VBA sorting with custom order

Question

I am new to VBA and having this problem. Table position and value

A1 S+01a
A2 S+02a
A3 S+03a
A4 S-01a
A5 S-01b
A6 S-02a

Since this is generate by VBA, and I would like to order in this order

A1 S+01a
A2 S-01a
A3 S-01b
A4 S+02a
A5 S-02b
A6 S+03a

Sorting Rules will be

plus sign with smallest number behind
minus sign with the same number as plus sign (if exist)
(For minus sign only) alphabetical order of the last character

I would like to perform this action by VBA (since data length will be bigger) Any clue for this situation?

Thank you for the answer/clue.

user_4111774 · Answer

Since I am not sure how does your code handle the array (or collection) and you did not show me the actual code, I've written this POC but it is poorly coded. Basically I encode-decode the strings giving priority to what seems are your sorting criteria (including + and - signs).

Sub test()
Dim array_unsorted(1 To 6) As String
Dim i As Long

Dim recoded(1 To 6) As String
Dim temp As String
Dim target As String

array_unsorted(1) = "S+01a"
array_unsorted(2) = "S+02a"
array_unsorted(3) = "S+03a"
array_unsorted(4) = "S-01a"
array_unsorted(5) = "S-01b"
array_unsorted(6) = "S-02a"

For i = 1 To 6
    target = array_unsorted(i)
    temp = Replace(target, "+", "A")
    temp = Replace(target, "-", "Z")
    recoded(i) = Mid(temp, 3, 2) & Right(temp, 1) & target
Next

Call QuickSort(recoded, 1, 6)
   For i = 1 To 6
    s = Right(recoded(i), 5)
    Debug.Print s
    Next
End Sub


Public Sub QuickSort(ByRef vArray As Variant, inLow As Long, inHi As Long)

  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)

     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Wend

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Wend

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If

  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi

End Sub

At least it works and could be a starting point. Happy coding. Cheers!

user_8112776 · Answer

To add a custom sort order manually:

Select the range of data to sort
On the Data ribbon tab, click Sort
Click the Order drop-down, and choose Custom at the bottom of the list.

This brings you to custom sort dialog, where you can add you specific sort order, where it will then remain on the list.

See also: Sort data using a custom list

If there's a specific reason this must be done through VBA, I'd suggest Recording a Macro to Generate Code and then you can revise it as required.

Excel VBA sorting with custom order

2 Answers

To add a custom sort order manually: