MS Excel: Randomize column of strings into a grid of strings

It can be done with a helper column that creates a random order number from 1 to 100. With your names in A2:A101. In B2 put:

=AGGREGATE(15,7,ROW($1:$100)/(COUNTIFS($B$1:B1,ROW($1:$100))=0),RANDBETWEEN(1,100-COUNT($B$1:B1)))

And copy down.

This will randomly select a number between 1 and 100 with the k, in the AGGREGATE being RANDBETWEEN(1,100-COUNT($B$1:B1)). While the COUNTIFS($B$1:B1,ROW($1:$100))=0 makes sure we do not get duplicates.

Then we use INDEX/MATCH to find the value. Put this in the upper right corner of the grid:

=INDEX($A:$A,MATCH((ROW($A1)-1)*10+COLUMN(A$1),$B:$B,0))

As is it drug over and down it looks for 1-10 in the first row and 11-20 in the second and so on. And since the lookup column is randomized it will be random.

Then copy over 10 and down 10:

If one has office 365 Excel then the INDEX/MATCH can be replaced with this dynamic version that will spill the 10x10 automatically:

=INDEX(A:A,MATCH(SEQUENCE(10,10),B:B,0))

Assuming the names are stored in column A:

1. In the column B, apply the formula =RAND()
2. Copy and paste the resulting values into column B, overwriting the formula
3. In column C, apply the formula =RANK(B2, $B$2:$B$101). This will enable you to assign a number 1-100 to each name
4. Above your 10x10 grid, add the numbers 1-10. Do the same to the left of your 10x10 grid. These will serve as your row and column headers.

Now, assuming your row headers are in E2:E11 and your column headers are in F1:O1...

5. Enter the formula =INDEX($A$2:$A$101, MATCH(($E2-1)*10+F$1, $C$2:$C$101,0)) into cell F2 and drag across 10x10 grid