Randomly shuffle 20 rows, many times, row repetition allowed

[Al_Excel]

Hi,

I have 20 rows and 3 columns of numbers.
In the description below please note that row repetition is allowed:

(1) randomly pick one row from the original list and place it as the 1st row of a second 20-row list,
(2) randomly pick one row from the original list and place it as the 2nd row of a second 20-row list,
(3) randomly pick one row from the original list and place it as the 3rd row of a second 20-row list,
etc.
etc.
(20) randomly pick one row from the original list and place it as the 20th row of a second 20-row list.

Repeat the previous 20 calculations for a third 20-row list,
Repeat the previous 20 calculations for a fourth 20-row list,
Repeat the previous 20 calculations for a fifth 20-row list,
etc.
etc.
Repeat the previous 20 calculations for a 499th 20-row list,
Repeat the previous 20 calculations for a 500th 20-row list.

Does anyone know how to do this without VBA coding?

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Why not add a column with =RAND() in it?

Then all you would need to do is sort all the data by that column.

When you have done that once the values of RAND will have recalculated so re-sorting by the same column will give you the list in another order.

regards,

 

[Al_Excel]

Thank you for your reply.
I followed your suggestion but it seems that your idea would have to be done 500 times. Also, it seems that with your idea repetition of rows is not allowed.
Any hints of building an Excel formula that could be copied 500 times?

I was told to use the formula (the columns with the data are B, C, and D. The rows with the data are 26 to 45):
INDEX($B$26:$B$45,RANK(F26,$F$26:$F$45))
There are 2 problems with such formula:
1) That formula shuffles column B only.
2) After the first shuffle, all the other shuffles are the same.
Any suggestion to obtain a formula to shuffle rows 26 to 45, 500 times, row repetition allowed, is welcome.