Seating Plan

Thanks all. I think you're right that it's a step too far to fully automate it as required.

So to simplify it, I'm dropping the boy-girl-boy-girl requirement. Then, is it possible to combine randomness with some orderliness? By that I mean:

A.......B
-----------
1.......10
2.......6
3.......4
4.......14
5.......9
6.......7
7.......5

So column A is all the departments and Column B is the user count in each department. Can I do anything that will randomly populate rows in a specified column with a department number but only total the users count, and not put the same department number on following rows (except department 4 as they'll need to be together in 2 or 3's probably). To end up with 10X 1, 6X 2, 4X 3, 14X 4 etc.

I'm doing this at the moment, but there's no maximum for each number: =IF(ISBLANK($A12),"",RANDBETWEEN(1,7))

Thanks!