Using RAND or other function(possibly VBA) to randomize seating through multiple tables

[gulli09]

Hi,

I've got a problem which I initially thought would be very straightforward and that most likely I would be able to easily find a ready made template.

My conundrum is that I have a list of 150 names, and 12 times a year around a 100 of them get together on different tables, around 15 on each table.

Like I said around 100 of the 150 attend each event, which can be marked in a number of ways, for example by marking "x" in a column. When it comes to each event I want the excel sheet to randomize the persons through to each table as to not always having the same seating arrangement event after event.

It would obviously be optimal if I could each time, depending on how many attendees figure out how many tables I need and only randomize through to those tables, but that is something that would be possible to manually do each time.

Any help much appreciated, a template excel sheet which already does this would also work like a charm!

Regards,
Gulli

 

[gulli09]

Not sure if it would help but I can email the excel sheet I have so far if it helps, can't seem to figure out how to upload it here.

 

[StephenCrump]

Perhaps something like this:

B1: =COUNTIF(B5:B14,TRUE)
C5: =IF(B5,RAND(),"")
D5: =IF(B5,RANK(C5,C$5:C$14),"")
E5: =IF(B5,1+INT((D5-1)/B$2),"")

Excel 2010

	A	B	C	D	E
No attending
No per table
Name	Attending?
Aaron
Bill
Catherine
Doug
Eliza
Fatima
George
Helen
Indigo
Jack

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]Rand()[/TD]
[TD="align: right"]Rank[/TD]
[TD="align: right"]Table #[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.997729[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.488817[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.070842[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.370136[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.901444[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.356748[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]TRUE[/TD]
[TD="align: right"]0.672608[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet1
​