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.

Is there a reason this has to specifically be done with VBA, instead of "the easy way"? — ashleedawg
– ashleedawg, Commented Sep 18, 2018 at 7:18
You say the output is already generated by VBA, can you show some code? Would be easier to help you out then. — Lookaji
– Lookaji, Commented Sep 18, 2018 at 7:19
@ashleedawg The data was calculated by some procedure and store in an array. That's why I am asking for a vba solution. Thank you for the reply. — P. Tam
– P. Tam, Commented Sep 18, 2018 at 7:30
@lookaji it's read from a CSV and store inside the array. Normal value=Range("range").value was adopted. I am not the first one the handle this but i have to perform this order action — P. Tam
– P. Tam, Commented Sep 18, 2018 at 7:33
I would split the strings into the sorted parts; write it to a worksheet; sort the three columns in the priority order you want, and read it back into the array. Then delete the sheet. If you turn off screenupdating, you won't have visual flickering. — Ron Rosenfeld
– Ron Rosenfeld, Commented Sep 18, 2018 at 11:27

Lookaji · Accepted Answer · 2018-09-18 08:30:16Z

2

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!

answered Sep 18, 2018 at 8:30

Lookaji

1,04310 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

P. Tam Over a year ago

Thank you for the reply. I will try out later and if it works, I will mark as answer.

Lookaji Over a year ago

It is positional, so works mainly with fixed lenght strings. Give more details if you need more help :)

ashleedawg · Accepted Answer · 2018-09-18 07:35:19Z

1

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.

answered Sep 18, 2018 at 7:35

ashleedawg

21.9k9 gold badges82 silver badges120 bronze badges

1 Comment

P. Tam Over a year ago

thank you for the reply. The problem is the length will be different for different array. Which cannot be using this method (i.e. i have written 6 object inside the question but there will be more)

Collectives™ on Stack Overflow

Excel VBA sorting with custom order

2 Answers 2

2 Comments

To add a custom sort order manually:

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

To add a custom sort order manually:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related