Reorder/shuffle values in a row/column in Excel

Question

Is it possible to shuffle randomly, values in rows or columns in a excel sheet. Is there any function/macro for that? How do i do this?

-ad.

Answer 1

Hm... just as simple solution without programming. For example, you have a column of data (A):

23
78
12
78

Just add another one column - B. Each cell of column B must be a function RAND():

A     B
--    --
23    =RAND()
78    =RAND()
12    =RAND()
78    =RAND()

Now you can sort rows by column B and get desired solution.

Answer 2

Let's assume your values are in cells A1:A8

1. Enter the formula =RANDBETWEEN(1,100000)+ROW()/100000 in cells B1:B8
2. Enter the formula =RANK(B1,$B$1:$B$8) in cells C1:C8
3. Enter the formula =INDEX($A$1:$A$8,C1) in cells D1:D8

Every time you calculate (hit F9), a newly shuffled list will appear in cells D1:D8