Dynamic Sort of =FILTER results

[TheSubject]

Hi! As the name suggests, looking for help dynamically sorting data from a filter formula.

See below attached mini sheet.

Data is just an example, but in this example, column J (3pm) my venue is fullest, but the list provided is in order of when it was entered as per the source list. I need to rearrange dynamically according to the headcount of the booking (column AA) vs the number of seats at each table written in column A in the last number

I don't even know where to start! Google has provided non-dynamic approaches!

bookings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2	CAPACITY																									Date	20/04/2021
3	Times	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30			Shortlist
4	T1 - 6	dave	Empty	Empty	Empty	Empty	Empty	Jane	Empty	Tom	Empty	Empty	Empty	Empty	Empty	David	Empty	Lazarus	Empty	Empty	Empty	Empty	Empty			Name	Headcount	Arrival	Departure
5	T2 - 4									****																Tom	6	15:00	17:00
6	T3 - 2									DARREN																****	6	15:00	21:00
7	T4 - 6									JAMES																Lazarus	6	19:00	21:00
8	T5 - 4									Tony																Danny	6	20:00	22:00
9	T6 - 6									Eliza																Jane	4	14:00	18:00
10	T7 - 6									Jim																David	1	18:00	20:00
11	T8 - 4									sarah																DARREN	1	15:00	17:00
12	T9 - 4									liz																JAMES	1	15:00	17:00
13	T10 - 4									gerard																Tony	5	15:00	17:00
14	T11 - 4									hubert																Eliza	6	15:00	17:00
15	T12 - 4																									james	1	15:00	17:00
16	T13 - 4																									Jim	6	15:00	17:00
17																										sarah	2	15:00	17:00
18																										liz	1	15:00	17:00
19																										gerard	3	15:00	17:00
20																										hubert	3	15:00	17:00
21																										dave	3	11:00	13:00
Sheet1

Cell Formulas
Range	Formula
B4:I4,K4:W4,J4:J14	B4	=UNIQUE(IFERROR(FILTER($Z:$Z,$AB:$AB=B$3,"Empty"),""))
Z5:Z21	Z5	=FILTER(AG:AG,AH:AH=$AA$2)
AA5:AA21	AA5	=IF(Z5="","",INDEX(AI:AI,MATCH(Z5,AG:AG,0)))
AB5:AB21	AB5	=IF(Z5="","",INDEX(AJ:AJ,MATCH(Z5,AG:AG,0)))
AC5:AC21	AC5	=IF(Z5="","",INDEX(AL:AL,MATCH(Z5,AG:AG,0)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:W16	Cell Value	contains "Empty"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
AA1:AA21	Custom	=COUNTIF(AS2:BO501,">58")=0

Thank you so much!

 

[Eric W]

It took me a while to figure out what you're asking, but I think I have it. I had to sort the table column by capacity. Also, the formulas use the dynamic array functions, but they don't Spill. You'll have to drag the formulas down the column. But consider this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2	CAPACITY																									Date	4/20/2021
3	Times	11:00:00	11:30:00	12:00:00	12:30:00	13:00:00	13:30:00	14:00:00	14:30:00	15:00:00	15:30:00	16:00:00	16:30:00	17:00:00	17:30:00	18:00:00	18:30:00	19:00:00	19:30:00	20:00:00	20:30:00	21:00:00	21:30:00			Shortlist
4	T3 - 2									DARREN						David										Name	Headcount	Arrival	Departure
5	T2 - 4	dave						Jane		JAMES																Tom	6	15:00	17:00
6	T5 - 4									sarah																***	6	15:00	21:00
7	T8 - 4									liz																Lazarus	6	19:00	21:00
8	T9 - 4									gerard																Danny	6	20:00	22:00
9	T10 - 4									hubert																Jane	4	14:00	18:00
10	T11 - 4																									David	1	18:00	20:00
11	T12 - 4																									DARREN	1	15:00	17:00
12	T13 - 4																									JAMES	1	15:00	17:00
13	T1 - 6									Tom								Lazarus								Tony	5	15:00	17:00
14	T4 - 6									Tony																Eliza	6	15:00	17:00
15	T6 - 6									Eliza																james	1	15:00	17:00
16	T7 - 6									Jim																Jim	6	15:00	17:00
17																										sarah	2	15:00	17:00
18										Not enough tables																liz	1	15:00	17:00
19																										gerard	3	15:00	17:00
20																										hubert	3	15:00	17:00
21																										dave	3	11:00	13:00
Sheet1

Cell Formulas
Range	Formula
B4:W16	B4	=IFERROR(INDEX(FILTER($Z$5:$Z$21,($AA$5:$AA$21<=RIGHT($A4)+0)*(COUNTIF(B$3:B3,$Z$5:$Z$21)=0)*($AB$5:$AB$21=B$3)),1),"")
B18:W18	B18	=IFERROR(IF(COUNTIF(B4:B16,"?*")<>ROWS(UNIQUE(FILTER($Z$5:$Z$21,($AB$5:$AB$21=B3)))),"Not enough tables",""),"")

I added the row 18 formula to indicate there aren't enough tables. At 15:00, there were 5 parties needing a table of 6 when only 5 were available. Also, depending on how long the table is used, there could be an issue if another party came in at 15:30 needing a table.

But take a look and see what you think.

 

[jasonb75]

An alternative method, using the same principle as @Eric W in that the table column is sorted by capacity, I've also split the table and capacity into 2 separate columns.

XL2BB wouldn't let me post the entire thing as it 'exceeds the limit that can be posted on the forum' even though it is the same range, so I'm guessing the longer formulas pushed it over the edge. The snippet below should be enough to demonstrate the theory that I was working on, columns Z:AC are identical to the original sample with the exceptions of 1 name change so that the profanity filter didn't cause problems, and the headcount for Gerard increased to 6 in order to test the 'unallocated' formula in the bottom row. (I started with conditional formatting on the names in column z to highlight them as they were added to the table, but Eric's idea seems better).

One point to note, neither Eric or myself have allowed for identical names in column Z, the formulas are not case sensitive so JAMES and james are taken as the same person. There are functions that can allow for identical names in the source list, but with everything else that is needed I don't see them being a realistic option here.

There is likely room for improvement, but I think that you have 2 good options to explore.

test file 16.04.21.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Table	CAPACITY
3		Times	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30
4	T3	2									sarah	sarah	sarah	sarah
5	T10	4	dave	dave	dave	dave			Jane	Jane	Jane	Jane	Jane	Jane	Jane	Jane
6	T11	4									hubert	hubert	hubert	hubert
7	T12	4									DARREN	DARREN	DARREN	DARREN
8	T13	4									JAMES	JAMES	JAMES	JAMES
9	T2	4									liz	liz	liz	liz
10	T5	4
11	T8	4
12	T9	4
13	T1	6									Richard	Richard	Richard	Richard	Richard	Richard
14	T4	6									Tom	Tom	Tom	Tom
15	T6	6									Eliza	Eliza	Eliza	Eliza
16	T7	6									Jim	Jim	Jim	Jim
17	Unallocated	6									gerard, Tony
Sheet4

Cell Formulas
Range	Formula
C4:P16,C17	C4	=IFERROR(IF(XLOOKUP(B4,$Z$5:$Z$21,$AC$5:$AC$21,0)>C$3,B4,INDEX(SORT(FILTER($Z$5:$AC$21,($AA$5:$AA$21<=$B4)*($AB$5:$AB$21=C$3)*(COUNTIF($B$4:B$16,$Z$5:$Z$21)=0)*(COUNTIF(C$3:C3,$Z$5:$Z$21)=0)),{2,3,4},{-1,1,-1}),COUNT(FILTER($B$3:$B3,($B4<$B$3:$B3)*IF(B$3:B3="",TRUE,B$3:B3<>C$3:C3),""))+1,1)),"")
B17	B17	=MAX($AA$5:$AA$21)
D17:P17	D17	=IFERROR(TEXTJOIN(", ",1,INDEX(SORT(FILTER($Z$5:$AC$21,($AA$5:$AA$21<=$B17)*($AB$5:$AB$21=D$3)*(COUNTIF($B$4:B$16,$Z$5:$Z$21)=0)*(COUNTIF(D$3:D16,$Z$5:$Z$21)=0)),{2,3,4},{-1,1,-1}),0,1)),"")

 

[TheSubject]

Thanks, both of you - I'm still working on it, but you've given me a lot to go on!
@jasonb75 I like you're "unallocated" as opposed to just not enough tables, so will try to work that in from what I have from @Eric W already.

I've modified Eric W's formula so (apart from the opening slot at 11:00) they now read:

=IF(ISERROR(F11),(INDEX(FILTER($Z$5:$Z$21,($AA$5:$AA$21<=RIGHT($A11)+0)*(COUNTIF(G$3:G10,$Z$5:$Z$21)=0)*($AB$5:$AB$21=G$3)),1)),IF(INDEX($AC:$AC,MATCH(F11,$Z:$Z,0))>G$3,F11,(INDEX(FILTER($Z$5:$Z$21,($AA$5:$AA$21<=RIGHT($A11)+0)*(COUNTIF(G$3:G10,$Z$5:$Z$21)=0)*($AB$5:$AB$21=G$3)),1))))

Which gives peoples stay length, and a ton of errors instead of blanks, but I've made a front sheet, that removes the million errors that pop up but otherwise equals it. Maybe a little less elegant, but I'm learning!!

Of course, I emailed this to myself at work and opened my macbook and it didn't work!! But I'll try to update excel or just use it on the office PC if not.

 

[Eric W]

Glad we could help! Let us know if you have any follow up questions.

 

[Eric W]

It occurred to me that you could shorten the way you rewrote your formula, like:

Book2
	Z	AA	AB	AC
1
2	Date	4/20/2021
3	Shortlist
4	Name	Headcount	Arrival	Departure
5	Tom	6	15:00	17:00
6	Richard	6	15:00	21:00
7	Lazarus	6	15:00	21:00
8	Danny	6	20:00	22:00
9	Jane	4	14:00	18:00
10	David	1	18:00	20:00
11	DARREN	1	15:00	17:00
12	JAMES	1	15:00	17:00
13	Tony	5	15:00	17:00
14	Eliza	6	15:00	17:00
15	james	1	15:00	17:00
16	Jim	6	15:00	17:00
17	sarah	2	15:00	17:00
18	liz	1	15:00	17:00
19	gerard	3	15:00	17:00
20	hubert	3	15:00	17:00
21	dave	3	11:00	13:00
Sheet1

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2	CAPACITY
3	Times	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30
4	T3 - 2									DARREN, 17:00						David, 20:00
5	T2 - 4	dave, 13:00						Jane, 18:00		JAMES, 17:00
6	T5 - 4									sarah, 17:00
7	T8 - 4									liz, 17:00
8	T9 - 4									gerard, 17:00
9	T10 - 4									hubert, 17:00
10	T11 - 4
11	T12 - 4
12	T13 - 4
13	T1 - 6									Tom, 17:00
14	T4 - 6									Richard, 21:00
15	T6 - 6									Lazarus, 21:00
16	T7 - 6									Tony, 17:00
17
18	Unallocated									Eliza, Jim
Sheet1

Cell Formulas
Range	Formula
B4:W16	B4	=IFERROR(TEXTJOIN(", ",1,TEXT(INDEX(FILTER($Z$5:$AC$21,($AA$5:$AA$21<=RIGHT($A4)+0)*(COUNTIF(B$3:B3,$Z$5:$Z$21&"*")=0)*($AB$5:$AB$21=B$3)),1,{1,4}),"h:mm;@")),"")
B18:W18	B18	=IFERROR(TEXTJOIN(", ",1,INDEX(FILTER($Z$5:$AC$21,($AB$5:$AB$21=B$3)*(COUNTIF(B$4:B$16,$Z$5:$Z$21&"*")=0)),0,1)),"")