Formula to Evenly Distribute Guests on a Seating Plan

[markmogli]

Hello,

I have a spreadsheet where column A has the company name and column B has the total guests booked to attend the event. I need to allocate the guests to tables where the smallest can be a table of 7 and the largest can be a table of 14. I also need to allocate the party to the smallest number of tables possible.

A party of 15 guests will be seated as 1 x table of 7 and 1 x table 8.


A party of 20 guests will be seated as 2 x tables of 10.


A party of 36 guests can be seated as either

3 x tables of 12

or

1 x table of 13, 1 x table of 12 and 1 x table of 11


A party of 100 guests can be seated as:

10 tables of 10

but they could also be seated as:

6 x tables of 14 and 2 x tables of 8


I have 2,200 guests to seat and a maximum of 220 tables. I want the least possible tables and as bonus points, I only have 30 tables which will take 13 or 14 guests, the other 190 tables seat a max of 12.


Edit - There can be 100 companies attending the event with bookings ranging from just 7 guests to say 275 guests. Each company can only sit on tables with their own guests, there can be no mixed tables.

[AliGW]

Welcome to the forum.

Please attach the workbook - there are instructions at the tope of the page explaining how to do this.

There was a similar thread recently - whilst waiting to see your file, I'll see if I can find it.

EDIT: Here's that other thread: https://www.excelforum.com/excel-for...th-sample.html

[markmogli]

Hi there AliGW,

I've attached a sample workbook showing how I'm doing the calculations manually at the moment. I checked out the link it seems useful but I'm not sure how I can link it to what I'm trying to achieve.

Any help greatly appreciated.

Thank you

[markmogli]

Here is an updated workbook