Reverse sort within cell option for this VBA code

[sdalrymple]

Hello! I'm new to VBA and first would like to thank everyone posting codes that anyone can use!!

I've found this very useful code to alphabetically sort words within cell separated by a comma. I'm wondering if i can tweak this to reverse sort? (Z-A instead of A-Z)

---

Function StrSort(ByVal sInp As String, Optional bDescending As Boolean = False) As String
' sorts a comma-delimited string
Dim asSS() As String ' substring array
Dim sSS As String ' temp string for exchange
Dim n As Long
Dim i As Long
Dim j As Long
Dim Val1 As Long
Dim Val2 As Long
asSS = Split(sInp, ",")
n = UBound(asSS)
For i = 0 To n
asSS(i) = Trim(asSS(i))
Next i
If n <= 1 Then
StrSort = sInp
Else
For i = 0 To n - 1
For j = i + 1 To n
If InStr(asSS(j), "-") > 0 Then
Val1 = Val(Left(asSS(j), InStr(asSS(j), "-") - 1))
Else
Val1 = Val(asSS(j))
End If
If InStr(asSS(i), "-") > 0 Then
Val2 = Val(Left(asSS(i), InStr(asSS(i), "-") - 1))
Else
Val2 = Val(asSS(i))
End If
If (Val1 < Val2) Xor bDescending Then
sSS = asSS(i)
asSS(i) = asSS(j)
asSS(j) = sSS
End If
Next j
Next i
StrSort = Join(asSS, ", ")
End If
End Function

 

[sdalrymple]

updating the format of code I posted above:

Function StrSort(ByVal sInp As String, Optional bDescending As Boolean = False) As String
'   sorts a comma-delimited string
    Dim asSS() As String ' substring array
    Dim sSS As String ' temp string for exchange
    Dim n As Long
    Dim i As Long
    Dim j As Long
    Dim Val1 As Long
    Dim Val2 As Long
    asSS = Split(sInp, ",")
    n = UBound(asSS)
    For i = 0 To n
        asSS(i) = Trim(asSS(i))
    Next i
    If n <= 1 Then
        StrSort = sInp
    Else
        For i = 0 To n - 1
            For j = i + 1 To n
                If InStr(asSS(j), "-") > 0 Then
                    Val1 = Val(Left(asSS(j), InStr(asSS(j), "-") - 1))
                Else
                    Val1 = Val(asSS(j))
                End If
                If InStr(asSS(i), "-") > 0 Then
                    Val2 = Val(Left(asSS(i), InStr(asSS(i), "-") - 1))
                Else
                    Val2 = Val(asSS(i))
                End If
                If (Val1 < Val2) Xor bDescending Then
                    sSS = asSS(i)
                    asSS(i) = asSS(j)
                    asSS(j) = sSS
                End If
            Next j
        Next i
        StrSort = Join(asSS, ", ")
    End If
End Function

 

[Scott Huish]

I couldn't actually get your function to work for me correctly, but there does appear to be an option to put TRUE as the second parameter for sorting the way you want.

 

[sdalrymple]

I couldn't actually get your function to work for me correctly, but there does appear to be an option to put TRUE as the second parameter for sorting the way you want.

Thanks for the response!
The code allows me to have a "StrSort" function:

=StrSort(cell)

 

[Scott Huish]

Here's a different UDF that still allows for TRUE as an optional second parameter to sort descending:

Function StrSort(sInput As String, Optional sortOptions = False)
Dim t, u
With CreateObject("System.Collections.ArrayList")
    For Each t In Split(sInput, ",")
        .Add t
    Next
    .Sort
    If sortOptions Then u = .Reverse
    u = .toarray
StrSort = Join(u, ",")
End With
End Function

Excel 2010
	A	B
1	scott,bill,john	bill,john,scott
2	scott,bill,john	scott,john,bill
Sheet1

Cell Formulas
Range	Formula
B1	=StrSort(A1)
B2	=StrSort(A2,TRUE)

 

[Rick Rothstein]

If you are using a PC (not a Mac), then here is an easier way to sort your data. Note this method will do both an ascending or descending sort. The function has two required and one optional argument. The first is the text string whose elements you want to sort... the second is the delimiter found between the elements you want to sort... the optional third argument is a Boolean whose default is False meaning an ascending sort, pass it a True value to make the sort descending. Since you want a descending sort, you would specify True for the third argument. Here is the code...

[table="width: 500"]
[tr]
	[td]Function SortText(Text As String, Delimiter As String, Optional ReverseIt As Boolean) As String
  Dim X As Long, Data() As String
  Data = Split(Text, Delimiter)
  With CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Data)
      .Add Application.Trim(Data(X))
    Next
    .Sort
    If ReverseIt Then .Reverse
    SortText = Join(.ToArray, ",")
  End With
End Function[/td]
[/tr]
[/table]

EDIT NOTE
---------------------
I see that Scott posted basically the same function as I did a little earlier. I am leaving my method in the thread as I used a different For..Next loop than he did so it shows the reader two different ways to load of the ArrayList. Also, rather than assigning the outputted array to a variable (the "u" variable in Scott's code) which in turn is fed into the Join function, my code embeds the outputted array directly in the Join function thus skipping one step.

 

[sdalrymple]

THANK YOU!!!! It worked!!!!!!