Click here for important information about Computing.net.

Solved How to sort rows randomly in Microsoft Excel?

Tags:
Microsoft Office 2007 enterprise
excel
randomly
sort
rows
random
July 15, 2010 at 17:11:29
Specs: Windows XP SP3 Pro, ???
I have a table sorted from A - Z, I need is to sort it in random values, ie, without following any criteria, for example, A, H, J, A, K, L, S, O , R, H, P, G ...., are 63 rows.
How I can do it? I have Microsoft Office 2007 ... or with which program I can do it and export the table to Microsoft Office Word.

See More: How to sort rows randomly in Microsoft Excel?

Report •

#1
July 15, 2010 at 18:40:40
✔ Best Answer
Let's say you have a list of values in Column A.

In Column B, next to these values enter =RAND() to get something like this:

A   0.511711023
B   0.500532924
C   0.337966212
D   0.982653942
E   0.042077447
F   0.293538852
G   0.875436716

Now sort Column A and B on Column B

Since RAND() will recalculate every time the sheet recalculates, you can keep sorting on Column B and get a different order every time.


Report •

#2
July 16, 2010 at 05:32:31
Since I assume you will want to randomly sort Column A more than once, you could use a macro to simplify the task.

In this case I used a SelectionChange macro so it will ask you if you want to sort Column A whenever you select any cell in Column A.

You could also use a standard macro with a button or some other option to trigger the code.

(Hide Column B so that the random numbers don't show up.)


Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mySort As Integer
 If Target.Column = 1 Then
  mySort = MsgBox("Sort Column A?", vbYesNo)
   If mySort = vbYes Then
    Columns("A:B").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
   End If
 End If
End Sub




Report •
Start a discussion
Related Solutions
See More ↓


Ask Question