Populate table (25 cells) randomly with list of 5 people

[cRx45]

I've searched the forum for this but can't find it. If the answer exists please point me to the right thread.

I have a list of 5 people who I want to randomly put in a table consisting of columns Mon - Friday and rows for Week 1-5 (so 25 blank cells).

How do I go about randomly filling those 25 cells with the 5 people?

I can randomize the 5 people from 1 - 5 using the rand() & rank() formulae, but can't work out how/if to use them to fill the table randomly with those 5 people.

Any ideas?

Hope that made sense.

 

[tusharm]

Several options at Random Selection

I've searched the forum for this but can't find it. If the answer exists please point me to the right thread.

I have a list of 5 people who I want to randomly put in a table consisting of columns Mon - Friday and rows for Week 1-5 (so 25 blank cells).

How do I go about randomly filling those 25 cells with the 5 people?

I can randomize the 5 people from 1 - 5 using the rand() & rank() formulae, but can't work out how/if to use them to fill the table randomly with those 5 people.

Any ideas?

Hope that made sense.

 

[cRx45]

Several options at Random Selection

Thanks Tusharm, but that looks like it's only showing how to randomize and rank lists. It's not really helpful in my situation (that I can see).

 

[cRx45]

Here's what I'm trying to do:

Not sure if Columns A or C are needed to fill in cells F2:J6

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Rank	Name	Rand			Mon	Tues	Wed	Thur	Fri
2	2	Person 1	0.325		Week 1	#N/A	#N/A	#N/A	#N/A	#N/A
3	1	Person 2	0.041		Week 2	#N/A	#N/A	#N/A	#N/A	#N/A
4	3	Person 3	0.393		Week 3	#N/A	#N/A	#N/A	#N/A	#N/A
5					Week 4	#N/A	#N/A	#N/A	#N/A	#N/A
6					Week 5	#N/A	#N/A	#N/A	#N/A	#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) A2 =IFERROR(RANK(C2,C$2:C$4,1), "") A3 =IFERROR(RANK(C3,C$2:C$4,1), "") A4 =IFERROR(RANK(C4,C$2:C$4,1), "") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[tusharm]

It is relevant...here's how...

In F2:J6 enter the formula =RAND()

In L2 enter the formula =RANK.EQ(F2,$F2:$J2).
Copy L2 to the rest of L2:P6.

In R2 enter the formula =INDEX($B$2:$B$6,L2)
Copy R2 to the rest of R2:U6.

If you want, you could combine the formulas in L:P and R:U but the above is easier to understand.

Here's what I'm trying to do:

Not sure if Columns A or C are needed to fill in cells F2:J6

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Rank	Name	Rand			Mon	Tues	Wed	Thur	Fri
2	2	Person 1	0.325		Week 1	#N/A	#N/A	#N/A	#N/A	#N/A
3	1	Person 2	0.041		Week 2	#N/A	#N/A	#N/A	#N/A	#N/A
4	3	Person 3	0.393		Week 3	#N/A	#N/A	#N/A	#N/A	#N/A
5					Week 4	#N/A	#N/A	#N/A	#N/A	#N/A
6					Week 5	#N/A	#N/A	#N/A	#N/A	#N/A

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J2 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J3 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J4 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J5 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) F6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) G6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) H6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) I6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) J6 =VLOOKUP(RAND(), $A$1:$B$4, 2, TRUE) A2 =IFERROR(RANK(C2,C$2:C$4,1), "") A3 =IFERROR(RANK(C3,C$2:C$4,1), "") A4 =IFERROR(RANK(C4,C$2:C$4,1), "") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[cRx45]

Oh wow! That worked perfectly.

I didn't think of it in that way with the 2 helper tables.

Thank you very much.

 

[tusharm]

You are welcome.

Oh wow! That worked perfectly.

I didn't think of it in that way with the 2 helper tables.

Thank you very much.