CODEWITHSUNDEEP
excelexcel-formula
Pear
Pear

Reputation: 31

Use excel to generate table assignments for each person

I have a list of 120 attendees. I would like to randomly assign each attendee to a table. There are 10 tables. I would like to ask Excel to generate a number, 1-10 for each attendee.

I realize that I can do this manually, but I need to do this for 13 different events.

Upvotes: 3

Views: 331

Answers (2)

jblood94
jblood94

Reputation: 17011

Enter this in a 1-column by 120-row range to get a list of 120 random table assignments, with each table number appearing exactly 12 times (commit with ctrl+shift+enter):

=MOD(ROUND(MOD(SMALL(RANDBETWEEN(0,1e12*(ROW(INDIRECT("1:120"))>0))+ROW(INDIRECT("1:120"))/1000,ROW(INDIRECT("1:120"))),1)*1000,0),10)+1

Get a new set of assignments by pressing F9.

Upvotes: 0

jblood94
jblood94

Reputation: 17011

If you put the list of 120 attendees in column A, starting at A2, enter this formula into B2 and instead of hitting just enter, commit with ctrl+shift+enter:

=LARGE(ROW(INDIRECT("1:120"))*NOT(COUNTIF($B$1:B1,ROW(INDIRECT("1:120")))),RANDBETWEEN(1,121-ROW(B1)))

Then put this formula in C2 (no need for ctrl+shift+enter):

=MOD(B2,10)+1

Fill B2 and C2 down to row 121. Column C will have the random table assignments, with 12 assigned to each table. Get a new set of assignments by pressing F9.

https://www.get-digital-help.com/2009/07/03/how-to-create-a-random-list-of-unique-numbers-in-excel/

Upvotes: 1

Related Questions