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
1	No attending	7
2	No per table	4
3
4	Name	Attending?	Rand()	Rank	Table #
5	Aaron	TRUE	0.997729	1	1
6	Bill
7	Catherine	TRUE	0.488817	4	1
8	Doug	TRUE	0.070842	7	2
9	Eliza
10	Fatima	TRUE	0.370136	5	2
11	George
12	Helen	TRUE	0.901444	2	1
13	Indigo	TRUE	0.356748	6	2
14	Jack	TRUE	0.672608	3	1

<tbody>
</tbody>

Sheet1
​