I'm populating an Excel array using an array formula and want the results to be sorted. If I attempt to sort (using data/sort) I get the error "You can't change part of an array". The only way I can sort it is by copying the array and pasting it as values, then sorting that copy. Is there a better way.
Steps to replicate:
In a blank worksheet, select range A1:A10 and insert an array formula that gives numeric results, e.g. =RAND(). The range is populated with values. Try to sort the range using Data/Sort and you get the above error.
"In place"? I doubt it's possible with excel function as it will cause self/circular referencing.
If you select "A1:A10" and enter an array(CSE) formula = Rand() then it can be sorted with following Array (CSE) formula in cell "D1" and copied down. This will sort the array in ascending order. To sort it in descending order replace ",1)%" with ",0)%"
=INDEX($A$1:$A$10,MATCH(SMALL(NPV(-RANK.EQ($A$1:$A$10,$A$1:$A$10,1)%,$A$1:$A$10)*100+ROW($A$1:$A$10),ROWS($A$1:A1)),NPV(-RANK.EQ($A$1:$A$10,$A$1:$A$10,1)%,$A$1:$A$10)*100+ROW($A$1:$A$10),0))
Reference to this answer from shrivallabha.redij
For "In place" array function refer to UDF below. Select "H1:H10" and enter Array (CSE) Formula =SortRandArr(H1:H10,1)
for descending sort or =SortRandArr(H1:H10,0)
for ascending sort.
Function SortRandArr(arrSizeRng As Range, Optional srtCriteria = 0)
'arrSizeRng is range of the same size of desired one dimensional array
'srtCriteria is criteria to sort; 0 or nothing for Ascending, Other digit for descending.
Application.Volatile
Dim Lb As Long, Ub As Long, i As Long, j As Long
Dim arr
ReDim arr(arrSizeRng.Cells.Count - 1)
For x = LBound(arr) To UBound(arr)
arr(x) = Round(Rnd(), 4)
Next
Lb = LBound(arr): Ub = UBound(arr)
If srtCriteria = 0 Then
For i = Lb To Ub - 1
For j = i + 1 To Ub
If Val(arr(i)) > Val(arr(j)) Then
strTemp = arr(i)
arr(i) = arr(j)
arr(j) = strTemp
End If
Next j
Next i
Else
For i = Lb To Ub - 1
For j = i + 1 To Ub
If Val(arr(i)) < Val(arr(j)) Then
strTemp = arr(i)
arr(i) = arr(j)
arr(j) = strTemp
End If
Next j
Next i
End If
SortRandArr = Application.Transpose(arr)
End Function
For one dimensional VBA sort referred to this link