Randomly shuffle 20 rows, many times, row repetition allowed

Al_Excel

New Member
Joined
May 30, 2016
Messages
2
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
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

New Member
Joined
May 30, 2016
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,952
Messages
5,471,673
Members
406,776
Latest member
Sushantsingh1

This Week's Hot Topics

Top