VBA sort string array function

[cgmojoco]

I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?

Function Alphabetize(ByVal sText As String) As String
   Dim sWords() As String, sTemp As String
   Dim i As Long, j As Long, n As Long
   
   '-- clean up text
   For i = 1 To Len(sText)
      Select Case Mid$(sText, i, 1)
         Case " ", "a" To "z", "A" To "Z"
         Case Else: Mid$(sText, i, 1) = " "
      End Select
   Next
   '-- remove leading and trailing spaces
   sText = Trim(sText)
   '-- remove multi spaces
   Do While InStr(sText, "  ")
      sText = Replace(sText, "  ", " ")
   Loop
   '-- split text by space
   sWords = Split(sText)
   n = UBound(sWords)
   If n = 0 Then '-- only one word
      Alphabetize = sText
   Else
      '-- sort array
      For i = 0 To n - 1
         For j = i + 1 To n
            If sWords(i) > sWords(j) Then
               sTemp = sWords(i)
               sWords(i) = sWords(j)
               sWords(j) = sTemp
            End If
         Next
      Next
      Alphabetize = Join(sWords, ",")
   End If
End Function

 

[jindon]

Change last argument to True.

 

[jindon]

And you also need to add If clause like

End With
If n > 0 Then
    VSortM b, 1, n, 2, myOrd   '<- Yup this one too
    For i = 1 To n
        VLookUpMulti = VLookUpMulti & IIf(vLookUpMulti = "", "", myJoin) & b(i, 1)
    Next
End If
End Function

 

[cgmojoco]

Deleted

 

[cgmojoco]

I think I jacked this up:

'*************************************************************************************************
'**********Multiple V Lookup**********************************************************************
'*************************************************************************************************
Function muvlookup(ByVal strIndex As String, ByVal Rng As Range, _
                 Optional ref As Integer = 1, Optional myJoin As String = " ", _
                 Optional myOrd As Boolean = True) As String
Dim a, b(), i As Long, n As Long
a = Rng.Value
ReDim b(1 To UBound(a, 1), 1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 1)
        If a(i, 1) = strIndex Then
            If Not .Exists(a(i, ref)) Then
                .Add a(i, 2), Nothing
                n = n + 1: b(n, 1) = a(i, 2)
                b(n, 2) = IIf(IsNumeric(a(i, 2)), a(i, 2), UCase(a(i, 2)))
            End If
        End If
    Next
End With

If n > 0 Then
    VSortM b, 1, n, 2, myOrd   '<- Yup this one too
    For i = 1 To n
        muvlookup = muvlookup & IIf(muvlookup = "", "", myJoin) & b(i, 1)
    Next
End If
End Function
Sub VSortM(ary, LB, UB, ref, myOrd)
Dim i As Long, ii As Long, iii As Long, M, temp
i = UB: ii = LB
M = ary(Int((LB + UB) / 2), ref)
Do While ii <= i
    If myOrd Then
        Do While ary(ii, ref) < M: ii = ii + 1: Loop
        Do While ary(i, ref) > M: i = i - 1: Loop
    Else
        Do While ary(ii, ref) > M: ii = ii + 1: Loop
        Do While ary(i, ref) < M: i = i - 1: Loop
    End If
    If ii <= i Then
        For iii = LBound(ary, 2) To UBound(ary, 2)
            temp = ary(ii, iii): ary(ii, iii) = ary(i, iii): ary(i, iii) = temp
        Next
        i = i - 1: ii = ii + 1
    End If
Loop
If LB < i Then VSortM ary, LB, i, ref, myOrd
If ii < UB Then VSortM ary, ii, UB, ref, myOrd
End Sub

Why isn't it working?

 

[cgmojoco]

Cannot have only one column and return in same column or go left in a column that is why I thought not working...

 

[cgmojoco]

Sorry for multiple posts...
Can this be amended to allow to select results column be one to the left of the lookup column? like index/match?

 

[factofthematter]

try
=VLookUpMulti(A1, C1:D100, 2, ",", False)

Function VLookUpMulti(ByVal strIndex As String, ByVal rng As Range, _
                 Optional ref As Integer = 1, Optional myJoin As String = " ", _
                 Optional myOrd As Boolean = True) As String
Dim a, b(), i As Long, n As Long
a = rng.Value
ReDim b(1 To UBound(a,1), 1 To 2)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a,1)
        If a(i,1) = strIndex Then
            If Not .exists(a(i, ref)) Then
                .add a(i,2), Nothing
                n = n + 1 : b(n,1) = a(i,2)
                b(n,2) = IIf(IsNumeric(a(i,2)),a(i,2), UCase(a(i,2)))
            End If
        End If
    Next
End With
VSortM b, 1, n, 2, False
For i = 1 To n
    VLookUpMulti = VLookUpMulti & IIf(vLookUpMulti = "", "", myJoin) & b(i, 1)
Next
End Function
 
Sub VSortM(ary, LB, UB, ref, myOrd) 
Dim i As Long, ii As Long, iii As Long, M, temp 
i = UB : ii = LB 
M = ary(Int((LB+UB)/2),ref) 
Do While ii <= i 
    If myOrd Then 
        Do While ary(ii,ref) < M : ii = ii + 1 : Loop 
        Do While ary(i,ref) > M : i = i - 1 : Loop 
    Else 
        Do While ary(ii,ref) > M : ii = ii + 1 : Loop 
        Do While ary(i,ref) < M : i = i - 1 : Loop 
    End If 
    If ii <= i Then 
        For iii = LBound(ary,2) To UBound(ary,2) 
            temp = ary(ii,iii) : ary(ii,iii) = ary(i,iii) : ary(i,iii) = temp 
        Next 
        i = i - 1 : ii = ii + 1 
    End If 
Loop 
If LB < i Then VSortM ary, LB, i, ref, myOrd 
If ii < UB Then VSortM ary, ii, UB, ref, myOrd 
End Sub

Thank you for this code jindon, you saved me so much time.

 

[cgmojoco]

Well, it has been years and I am had used this from time to time and it has saved countless hours, and now there is the MacOS on M1 chip which at this time is probably the most efficient processor. Unfortunately, Scripting.Dictionary is not supported on MacOS, and so this won't work. Is anyone willing to post a version that works without Scripting.Dictionary? And while we are at it, is there a way to look to another column, not just to the column to the right in the array? (similar to what you can do with index/match and return results from any column in the array). Thanks in advance.

 

[Joe4]

Jindon has not posted here since 2009, so I doubt he will see your question.

I think you stand a much better chance of getting help by posting a new thread, so it appears as an unanswered question in the "Unanswered threads" list (and will get a lot more "looks").
You can include a link to this thread in your new question, if you feel it would be helpful.