1

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.

nakb
  • 321
  • 1
  • 3
  • 16
  • 1
    You'll need to change the array formula to return its results sorted – chris neilsen May 25 '20 at 10:35
  • Thanks. Could you give an example of how I would need to change my existing {=RAND()} formula to do that? – nakb May 25 '20 at 11:44
  • If you have the new [Dynamic Array](https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2) features, try `=Sort(RandArray(10,1))` – chris neilsen May 25 '20 at 19:52

1 Answers1

1

"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

enter image description here

Naresh
  • 2,984
  • 2
  • 9
  • 15