If I have values in cell A2=2,4,8,6,12,19,18,23,35,78,101,38,30,205,2
And I want to sort by smallest to largest or largest to smallest in cell B2. then my desired result should be =2,2,4,6,8,12,19,18,23,30,35,38,78,101,101,205 or,Large to small= 205,101,101,78,38,35,30,23,18,19,12,8,6,4,2,2 if I have textvaluse like in A3= WPN/01,AFF/02,PROP/4,ENG/03 Then I want to sort alphabetically my desired result should be in cell B3=AFF/02,ENG/03,PROP/4,WPN/1

-
See if this helps .. stackoverflow.com/questions/44077753/… ... and this ... stackoverflow.com/questions/152319/vba-array-sort-functionNaresh– Naresh2020-03-22 07:34:41 +00:00Commented Mar 22, 2020 at 7:34
-
@NareshBhople sir showing Compile Error in LBoundMilan Kumar– Milan Kumar2020-03-22 07:40:19 +00:00Commented Mar 22, 2020 at 7:40
Add a comment
|
1 Answer
Following function will work for numbers, text and alphanumeric (numbers and text both) strings. Default srtCriteria is set to 0. So, if it is 0 or not mentioned the array will be sorted ascending, else if srtCriteria = 1 then descending.
Function SortArr(myString As String, deLmt As String, Optional srtCriteria = 0)
'myString is deLmt seperated string
'srtCriteria is criteria to sort; 0 or nothing for Ascending, Other digit for descending.
Dim Lb As Long, Ub As Long, i As Long, j As Long
Dim arr, reverseArray
Dim strTemp As String
arr = Split(Trim(myString), deLmt)
Lb = LBound(arr)
Ub = UBound(arr)
For i = Lb To Ub - 1
For j = i + 1 To Ub
If IsNumeric(arr(i)) = True And IsNumeric(arr(j)) = True Then
If Val(arr(i)) > Val(arr(j)) Then
strTemp = arr(i)
arr(i) = arr(j)
arr(j) = strTemp
End If
Else
If (arr(i)) > (arr(j)) Then
strTemp = arr(i)
arr(i) = arr(j)
arr(j) = strTemp
End If
End If
Next j
Next i
If srtCriteria = 0 Then
SortArr = Join(arr, deLmt)
Else
ReDim reverseArray(Ub)
For i = 0 To Ub
reverseArray(i) = arr(Ub - i)
Next
SortArr = Join(reverseArray, deLmt)
End If
End Function
8 Comments
Naresh
Milan your doubt is valid.. If a string contains text and numbers it will work in absurd manner for numbers. So editing my answer.. MIX function gives better result.
Milan Kumar
Ok Great Sir It worked well.Thanks so much
Milan Kumar
Sir If I want to sort by large to small , then where edit in the formula
Naresh
edited my answer to reverse the sort.
Naresh
I am student myself... There are many gurus on this platform whom we can follow.
|