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.

 

[yky]

I don't see why this is a problem, let alone an Excel problem. Do you have some other constrains you did not say, e.g. maximizing the number of people from the same company in the same table together?

Let's say the 30 big tables can each seat 14 people. You fill them up first. you would have 420 less (2200-420=1780) people to consider. The 1780 people would take 148 tables with four people in another table (1780/12 = 148 with a remainder of 4). Since the minimum size of guests in a table is 7, the 1780 people would take 147 tables of 12 and two tables of 8. This way, you'd need 30 + 147 + 2 = 179 tables.

 

[xenou]

I was also thinking that this could be quickly solved using the same logic described in the previous post. Since you want the least number of tables to be used, its pretty clear that you just fill up the 30 tables with 14 guests, then fill up the remaining tables with 12 guest until you have less than 12 guests. Then fudge the remainder (take back 12 people from one of the tables and split them with the last 4 people to make two tables of 8).

As an excel problem this could be set up as a solver problem. But that is not something that can be explained in a post - you would want to google "Excel Solver" or "Excel solving minimization problems with constraints" or "Excel solving maximization problems with constraints". These or similar searches should set you up with some tutorials and examples. It's the kind of thing that you have to invest some time in learning first. But it's overkill in this particular case since the minimization is easily determined with just a few operations (fill up the tables with max guests until you run out of guests or tables, the only trick being at the end you might have to split guests into the two last tables in a balanced manner (two tables of 8 rather than one of 12 and one of 4).

 

[Eric W]

The basic algorithm described above is certainly a starting point, but it's surprisingly tricky in the details. Here's what I came up with:

Book1
	A	B	C	D
1	Number of guests		# of 14-guest tables	# of 12-guest tables
2	72		30	190
3
4	Minimum # of tables
5	6
6				Option 2 (average a 14-person table with the smallest table)
7	Table size	Count
8	14	5		4
9	13			0
10	12			0
11	11			0
12	10			0
13	9			0
14	8			2
15	7			0
16	6			0
17	5			0
18	4			0
19	3			0
20	2	1		0
21	1			0
22
23	Total guests:	72		72
24	Total tables	6		6
Sheet13

Cell Formulas
Range	Formula
A5	A5	=IF(A2<=14*C2,ROUNDUP(A2/14,0),C2+ROUNDUP((A2-C2*14)/12,0))
B8	B8	=IF(A2>=C2*14,C2,INT(A2/14))
B9	B9	=IF(AND(B8<C2,MOD(A2,14)=13),1,"")
B10	B10	=IF(A2>C2*14,INT((A2-C2*14)/12),IF($A$2-SUMPRODUCT($A$8:$A$9,$B$8:$B$9)=A10,1,""))
B11:B21	B11	=IF($A$2-SUMPRODUCT($A$8:$A$10,$B$8:$B$10)=A11,1,"")
D8:D21	D8	=IF($A$2<15,"",N(B8)-(A8=LOOKUP(999,$B$8:$B$21,$A$8:$A$21))-(A8=14)+(ROUNDUP((14+LOOKUP(999,$B$8:$B$21,$A$8:$A$21))/2,0)=A8)+(ROUNDDOWN((14+LOOKUP(999,$B$8:$B$21,$A$8:$A$21))/2,0)=A8))
B23	B23	=SUMPRODUCT(A8:A21,B8:B21)
B24, D24	B24	=SUM(B8:B21)
D23	D23	=SUMPRODUCT(A8:A21,D8:D21)

I've tested this a lot, and I think it works, but it's very possible I missed a case. This case here shows the results of 72 guests. The first pass shows 5 14-person tables and 1 2-person table. Averaging a full table with the remainder comes up with 4 14-person tables, and 2 8-person tables. Better, but better still would be 6 12-person tables.

Or consider 18 guests. Option 1 has 14 and 4, option 2 has 9 and 9. Option 1 has a large disparity in size. And if the event has people coming in couples, then option 2 doesn't work well either. 2 tables of 8 and 10 might be best.

And for larger groups, like the 2200 mentioned? At that size, I'd say the odds of some people not showing up is pretty high. I'd probably round up to the next full table size. Let the guests figure out where to sit.

In any event, I doubt there's an ideal algorithm that takes in to account every situation, but this could be a good starting point.

 

[lrobbo314]

Solver doesn't really come up with the best solutions in my testing. Especially since we don't know if there are any other factors. For example, maybe the big tables cost more than the smaller tables.

Either way, the only other combination that seems viable is 153 12-seat tables and 26 14-seat tables.

 

[markmogli]

I don't see why this is a problem, let alone an Excel problem. Do you have some other constrains you did not say, e.g. maximizing the number of people from the same company in the same table together?

Let's say the 30 big tables can each seat 14 people. You fill them up first. you would have 420 less (2200-420=1780) people to consider. The 1780 people would take 148 tables with four people in another table (1780/12 = 148 with a remainder of 4). Since the minimum size of guests in a table is 7, the 1780 people would take 147 tables of 12 and two tables of 8. This way, you'd need 30 + 147 + 2 = 179 tables.

Sorry, yes, the constraint is that all guests from each company must sit together - there can be no mixed tables.

 

[markmogli]

The basic algorithm described above is certainly a starting point, but it's surprisingly tricky in the details. Here's what I came up with:

Book1
	A	B	C	D
1	Number of guests		# of 14-guest tables	# of 12-guest tables
2	72		30	190
3
4	Minimum # of tables
5	6
6				Option 2 (average a 14-person table with the smallest table)
7	Table size	Count
8	14	5		4
9	13			0
10	12			0
11	11			0
12	10			0
13	9			0
14	8			2
15	7			0
16	6			0
17	5			0
18	4			0
19	3			0
20	2	1		0
21	1			0
22
23	Total guests:	72		72
24	Total tables	6		6
Sheet13

Cell Formulas
Range	Formula
A5	A5	=IF(A2<=14*C2,ROUNDUP(A2/14,0),C2+ROUNDUP((A2-C2*14)/12,0))
B8	B8	=IF(A2>=C2*14,C2,INT(A2/14))
B9	B9	=IF(AND(B8<C2,MOD(A2,14)=13),1,"")
B10	B10	=IF(A2>C2*14,INT((A2-C2*14)/12),IF($A$2-SUMPRODUCT($A$8:$A$9,$B$8:$B$9)=A10,1,""))
B11:B21	B11	=IF($A$2-SUMPRODUCT($A$8:$A$10,$B$8:$B$10)=A11,1,"")
D8:D21	D8	=IF($A$2<15,"",N(B8)-(A8=LOOKUP(999,$B$8:$B$21,$A$8:$A$21))-(A8=14)+(ROUNDUP((14+LOOKUP(999,$B$8:$B$21,$A$8:$A$21))/2,0)=A8)+(ROUNDDOWN((14+LOOKUP(999,$B$8:$B$21,$A$8:$A$21))/2,0)=A8))
B23	B23	=SUMPRODUCT(A8:A21,B8:B21)
B24, D24	B24	=SUM(B8:B21)
D23	D23	=SUMPRODUCT(A8:A21,D8:D21)

I've tested this a lot, and I think it works, but it's very possible I missed a case. This case here shows the results of 72 guests. The first pass shows 5 14-person tables and 1 2-person table. Averaging a full table with the remainder comes up with 4 14-person tables, and 2 8-person tables. Better, but better still would be 6 12-person tables.

Or consider 18 guests. Option 1 has 14 and 4, option 2 has 9 and 9. Option 1 has a large disparity in size. And if the event has people coming in couples, then option 2 doesn't work well either. 2 tables of 8 and 10 might be best.

And for larger groups, like the 2200 mentioned? At that size, I'd say the odds of some people not showing up is pretty high. I'd probably round up to the next full table size. Let the guests figure out where to sit.

In any event, I doubt there's an ideal algorithm that takes in to account every situation, but this could be a good starting point.

I think this might of use to me. Here is what I have. As you can see, a company with 60 guests can be seated two ways. Bookings can be from just 7 guests or 267 guests. I'm currently manually deciding how best to allocate each company.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1					1st set			2nd set			3rd set			Guest Sum			Table Sum
2		Customer	Guests		No of.	Guests		No of.	Guests		No of.	Guests		Guest Sum	Check		Table Sum
3		Company 1	119		11	10		1	9					119	True		12
4		Company 2	9		1	9								9	True		1
5		Company 3	60		6	10								60	True		6
6		Company 4	60		5	12								60	True		5
7		Company 5	15												False		0
8		Company 6	16												False		0
9		Company 7	12												False		0
10		Company 8	19												False		0
11		Company 9	29												False		0
12		Company 10	19												False		0
13		Company 11	9												False		0
14		Company 12	10												False		0
15		Company 13	11												False		0
16		Company 14	26												False		0
17		Company 15	14												False		0
Sheet1

Cell Formulas
Range	Formula
N3:N17	N3	=IF(((E3*F3)+(H3*I3)+(K3*L3))=0,"",(E3*F3)+(H3*I3)+(K3*L3))
O3:O17	O3	=EXACT(C3,N3)
Q3	Q3	=(E3+H3+K3)
Q4:Q17	Q4	=E4+H4+K4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
L3:L109	Cell Value	=7	text	NO
L3:L109	Cell Value	=8	text	NO
L3:L109	Cell Value	=9	text	NO
L3:L109	Cell Value	=10	text	NO
L3:L109	Cell Value	=11	text	NO
L3:L109	Cell Value	=12	text	NO
L3:L109	Cell Value	=13	text	NO
L3:L109	Cell Value	=14	text	NO
I3:I109	Cell Value	=7	text	NO
I3:I109	Cell Value	=8	text	NO
I3:I109	Cell Value	=9	text	NO
I3:I109	Cell Value	=10	text	NO
I3:I109	Cell Value	=11	text	NO
I3:I109	Cell Value	=12	text	NO
I3:I109	Cell Value	=13	text	NO
I3:I109	Cell Value	=14	text	NO
F3:F109	Cell Value	=7	text	NO
F3:F109	Cell Value	=8	text	NO
F3:F109	Cell Value	=9	text	NO
F3:F109	Cell Value	=10	text	NO
F3:F109	Cell Value	=11	text	NO
F3:F109	Cell Value	=12	text	NO
F3:F109	Cell Value	=13	text	NO
F3:F109	Cell Value	=14	text	NO
Q3	Expression	=Q3=AQ3	text	NO
N3:N109	Expression	=N3=C3	text	NO
O3:O110	Cell Value	contains "true"	text	NO

 

[markmogli]

And the final part of allocating the tables:

Book1
	U	V	W	X	Y																		AQ							AX	AY	AZ	BA
1	1	2	3	4	5																										To be printed on the night
2	1st table	2nd table	3rd table	4th table	5th table																		Total Tables								Table Numbers	Table Breakdown	Total No. of Tables
3	119	120	121	122	123																		5								1.1912E+14	11x10,1x9	5
4	127																						1								127	1x9	1
5	126	129	133	134	135																		5								1.26129E+14	6x10	5
6																							0									5x12	0
7																							0										0
8																							0										0
9																							0										0
Sheet1

Cell Formulas
Range	Formula
AQ3:AQ9	AQ3	=COUNTIF(U3:AN3,"<>")
AY3:AY9	AY3	=SUBSTITUTE(TRIM(U3&" "&V3&" "&W3&" "&X3&" "&Y3&" "&Z3&" "&AA3&" "&AB3&" "&AC3&" "&AD3&" "&AE3&" "&AF3&" "&AG3&" "&AH3&" "&AI3&" "&AJ3&" "&AK3&" "&AL3&" "&AM3&" "&AN3)," ",",")
AZ3:AZ9	AZ3	=SUBSTITUTE(TRIM(AR3&" "&AS3&" "&AT3)," ",",")
BA3:BA9	BA3	=AQ3

 

[lrobbo314]

You can give this VBA solution a shot. Seems to work pretty well.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Table Size
2	Company	Guests	Max		6	7	8	9	10	11	12	13	14	Guest Count	Diff	Table Count
3	Company A	312	22		0	0	0	2	0	0	0	0	21	312	0	23
4	Company B	180	12		0	0	0	0	0	0	1	0	12	180	0	13
5	Company C	140	10		0	0	0	0	0	0	0	0	10	140	0	10
6	Company D	200	14		0	0	0	2	0	0	0	0	13	200	0	15
7	Company E	188	13		1	0	0	0	0	0	0	0	13	188	0	14
8	Company F	128	9		0	0	2	0	0	0	0	0	8	128	0	10
9	Company G	364	26		0	0	0	0	0	0	0	0	26	364	0	26
10	Company H	268	19		0	0	2	0	0	0	0	0	18	268	0	20
11	Company I	220	15		0	0	0	0	1	0	0	0	15	220	0	16
12	Company J	200	14		0	0	0	2	0	0	0	0	13	200	0	15
13													Totals:	2200	0	162
Sheet5

Cell Formulas
Range	Formula
N3:N12	N3	=SUMPRODUCT($E$2:$M$2*E3:M3)
O3:O12	O3	=$B3-N3
P3:P12	P3	=SUM(E3:M3)
N13:P13	N13	=SUM(N3:N12)
A3:A12	A3	="Company " & CHAR(ROW()+62)
C3:C12	C3	=ROUNDDOWN(B3/14,0)

Sub TABLEORG()
Dim AR() As Variant: AR = Range("A3:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim dif As Integer: dif = 0
Dim total As Integer: total = 0

Range("M3:M" & UBound(AR) + 2).Value = Range("C3:C" & UBound(AR) + 2).Value

For i = 1 To UBound(AR)
    If AR(i, 2) < 14 Then
        Cells(i + 2, AR(i, 2) - 1).Value = 1
    Else
        total = AR(i, 3) * 14
        dif = AR(i, 2) - total
        If dif > 0 Then
            If dif >= 6 Then
                Cells(i + 2, dif - 1).Value = 1
            Else
                With Cells(i + 2, 13)
                    .Value = .Value - 1
                End With
                dif = (dif + 14) / 2
                Cells(i + 2, dif - 1).Value = 2
            End If
        End If
    End If
Next i
End Sub

 

[lrobbo314]

Had to update the code a bit. This code should be better.

Sub TABLEORG()
Application.ScreenUpdating = False
Dim AR() As Variant: AR = Range("A3:C" & Range("A" & Rows.Count).End(xlUp).Row).Value
Dim dif As Integer: dif = 0
Dim total As Integer: total = 0

Range("M3:M" & UBound(AR) + 2).Value = Range("C3:C" & UBound(AR) + 2).Value

For i = 1 To UBound(AR)
    If AR(i, 2) < 14 Then
        Cells(i + 2, AR(i, 2) - 1).Value = 1
    Else
        total = AR(i, 3) * 14
        dif = AR(i, 2) - total
        If dif > 0 Then
            If dif >= 6 Then
                Cells(i + 2, dif - 1).Value = 1
            Else
                With Cells(i + 2, 13)
                    .Value = .Value - 1
                End With
                
                If dif Mod 2 = 0 Then
                    dif = (dif + 14) / 2
                    Cells(i + 2, dif - 1).Value = 2
                Else
                    dif = Application.WorksheetFunction.RoundDown((dif + 14) / 2, 0)
                    Cells(i + 2, dif - 1).Value = 1
                    Cells(i + 2, dif).Value = 1
                End If
            End If
        End If
    End If
Next i
Application.ScreenUpdating = True
End Sub