random number generator formula

  • Thread starter Thread starter Becky
  • Start date Start date
B

Becky

Want to use worksheet with names and sort in random order. What is
formula for example if there are 25 names and what are the steps to
have worksheet copied with the new random order?
Have tried random number gen. add-in but haven't found correct
formula, help!
 
Have you used the RAND() function?

If i remember right, it may not be installed automatically so you might have
to add it on. But then just use RAND() next to each of the names, and sort
using that column.

If you want to have it copied with a random order, you can use the rand()
formula then record a macro of yourself copying the range and sorting it by
random row.
 
Try this set-up?

Assume the 25 names are in C1:C25

Put in B1: =RAND(), copy down B1:B25
Name the range B1:B25 as say: TBL

Select A1:A25
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)}

Now select A1:C25
name this range as say: NTable

Put in D1: =VLOOKUP(A1,NTable,3,FALSE)
copy down D1:D25

In D1:D25 will be a random shuffle of the names in C1:C25
without repeats. Press F9 for a new splash.

To freeze / copy the random names shuffled in D1:D25
to another destination

select D1:D25, click copy
click, say on cell F1
click Edit > Paste special > check "Values" > OK
(this will copy "frozen" values to F1:F25)
 
Back
Top