Sort / XLookUp running slow - any way to speed up calculations?

[dropkickweasel]

I have an array of around 2000 text strings.
I want to randomise the order of them on the click of a button.

My current solution is:

Column B; The original array Given the Named Range "Original_List"
Column C: A random value between 1 and 10000 generated with the following VBA code:

Sub RandomiseWordList()

Dim rng As Range
Set rng = Sheets("Working").Range("C2:C2001")

For Each Cell In rng
    Cell.Value = WorksheetFunction.RandBetween(1, 1000000)
    Next

End Sub

Column D: =SORT($C$2:$C$2001) Given the named range "Random_Number"

Column E: =XLOOKUP(D2,random_number,original_list)

All of this works fine, it just takes a little while to completely populate the newly ordered list in Column E.
Using INDEX-MATCH seems to be nominally quicker (~18s instead of ~20s).

Is there anything I can do to reduce the time taken for column E to populate when the macro is run?

Additionally, is there anything I can add to the macro to ensure that each number generated is unique?
If there is a way to do this, I could limit the range to 1 - 2315 if that makes any difference to the speed? (I arbitrarily picked 1000000 to attempt to reduce the number of duplicates).

Many thanks in advance.

 

[Fluff]

How use this

Sub RandomiseWordList()
   Sheets("Working").Range("C2:C2001").Value = Application.RandArray(2000)
End Sub

and then use sortby, rather than xlookup

 

[Fluff]

Or another option, get rid of the formulas & just use

Sub RandomiseWordList()
  With Application
      Sheets("Working").Range("C2:C2001").Value = .SortBy(Range("original_list"), .RandArray(2000))
  End With
End Sub

 

[dropkickweasel]

Or another option, get rid of the formulas & just use

Sub RandomiseWordList()
  With Application
      Sheets("Working").Range("C2:C2001").Value = .SortBy(Range("original_list"), .RandArray(2000))
  End With
End Sub

That's so much more efficient than what I was doing, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.