How can I randomize lots of rows in Excel?
For example I have an excel sheet with data in 3 rows.
1 A dataA
2 B dataB
3 C dataC
I want to randomize the row order. For example
2 B dataB
1 A dataA
3 C dataC
I could make a new column and fill it with random numbers using =RAND() and sort based on that column.
But is this the best way to do it? The RAND equation will provide up to a million random numbers and I have a quarter of a million rows so it seems like it would work.
Thanks
I searched for a bit and while this answer about randomizing columns is close it seems like way overkill.
I usually do as you describe:
Add a separate column with a random value (=RAND()
) and then perform a sort on that column.
Might be more complex and prettyer ways (using macros etc), but this is fast enough and simple enough for me.
Use Excel Online (Google Sheets).. And install Power Tools for Google Sheets.. Then in Google Sheets go to Addons tab and start Power Tools. Then choose Randomize
from Power Tools menu. Select Shuffle
. Then select choices of your test in excel sheet. Then select Cells in each row
and click Shuffle
from Power Tools menu. This will shuffle each row's selected cells independently from one another.
Here's a macro that allows you to shuffle selected cells in a column:
Option Explicit
Sub ShuffleSelectedCells()
'Do nothing if selecting only one cell
If Selection.Cells.Count = 1 Then Exit Sub
'Save selected cells to array
Dim CellData() As Variant
CellData = Selection.Value
'Shuffle the array
ShuffleArrayInPlace CellData
'Output array to spreadsheet
Selection.Value = CellData
End Sub
Sub ShuffleArrayInPlace(InArray() As Variant)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShuffleArrayInPlace
' This shuffles InArray to random order, randomized in place.
' Source: http://www.cpearson.com/excel/ShuffleArray.aspx
' Modified by Tom Doan to work with Selection.Value two-dimensional arrays.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim J As Long, _
N As Long, _
Temp As Variant
'Randomize
For N = LBound(InArray) To UBound(InArray)
J = CLng(((UBound(InArray) - N) * Rnd) + N)
If J <> N Then
Temp = InArray(N, 1)
InArray(N, 1) = InArray(J, 1)
InArray(J, 1) = Temp
End If
Next N
End Sub
You can read the comments to see what the macro is doing. Here's how to install the macro:
Now you can assign the "ShuffleSelectedCells" macro to an icon or hotkey to quickly randomize your selected rows (keep in mind that you can only select one column of rows).
Source: Stackoverflow.com