Random order in Excel?

  • Thread starter Thread starter Bends
  • Start date Start date
B

Bends

Hello,

I am creating a small spreadsheet in Excel that includes
a column of various book titles. Currently these titles
are listed in alphabetical order, but I would like to be
able to randomize the order in which they appear. Is
there a way of doing this in Excel?

Thank you,

Bends
 
One approach: Try this simple set-up?

Let's say you have 200 book titles listed in col A, A2 down

Put in B2: =RAND(), copy down to B201
Name the range B2:B201 as : TBL

Select C2:C201

Put in the formula bar: =RANK(TBL,TBL)
Hold down Ctrl + Shift keys, press Enter
(It's an array formula)

Done correctly, Excel will wrap curly braces around
the formula, viz: {=RANK(TBL,TBL)}
This formula will appear in each cell in C2:C201

In C2:C201 will be a random shuffle
of the numbers 1 - 200 without repeats

Now to extract the corresponding titles ..
Put in D2: =OFFSET($A$2,C2-1,)
Copy D2 down to D201

In D2:D201 will be a random shuffle
of the 200 titles in col A without repeats

Each press of F9 will re-generate another random shuffle

To freeze the random shuffle in D2:D201
just do a copy > paste special > values elsewhere
 
For a variety of options see the Excel/Tutorials/Random Selection page
of my web site.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
That's rather a long way to do it. Make your column of random numbers,
then sort both columns by the numbers.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
um .. not really, IMHO. It'll take only 30 sec to set-up,
with the benefit of an F9 key "activation" thereafter <g>
 
Sure. But it's two keys to re-sort. One to select in the RAND column,
the other on the A>Z button. Hey, there's more than one way to skin a cat.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Back
Top