DOUBLE FILTER function to return ONLY records with most recent date

[elcalvo]

I got valuable input from @Fluff and @RoryA on a FILTER function to return ONLY records with most recent date

• I'm using Office 365
• I have a defined table named Table1 with headers and its data below. I added a helper column in M (Include Partner) with the formula: =XLOOKUP([@Partner],check!$A$2#,check!$B$2:$B$4) which checks if the checkbox for a certain partner is checked (TRUE) or unchecked (FALSE)
• In another sheet (called “check”), I have made a filter section for Partners in A2: =SORT(UNIQUE(Table1[Partner])) with inserted checkboxes (insert – cell controls) which are TRUE or FALSE
• I want a formula that filters on "Meeting" in column F (Type) so it returns only rows with "Meeting" and only shows the most recent date as per column G (Last) AND that filters on the ticked boxes from the Partners filter section (see previous bullet)

I have tried to change RoryA's formula which was:

=LET(

z, exampledata,

a, FILTER(z, INDEX(z, , 6) = "Meeting"),

uc, UNIQUE(INDEX(a,0,4)),

b, SORT(SORT(a, 4, ), 7,-1 ),

c,INDEX(b,0,4),

result,REDUCE("",uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),

DROP(result,1)

)



And got as far as:

=VSTACK(

exampledata[#Headers],

LET(

z, exampledata,

a, SORT(SORT(FILTER(z, (INDEX(z, , 6) = "Meeting") * exampledata[Include Partner]), 4, 1), 7, -1),

uc, UNIQUE(INDEX(a, 0, 4)),

c, INDEX(a, 0, 4),

result, REDUCE("", uc, LAMBDA(a,i, VSTACK(a, TAKE(FILTER(a, c = i), 1)))),

DROP(result, 1)

)

)



Which returns a value error.

I’d appreciate if anyone can shine a light on this and maybe explain what I am doing wrong

To Do.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes	Include Partner
3	1	Partner 1	Other	Company A	Other	Meeting	31/01/2023	1	TRUE	02/02/2023	x	Notes 1	TRUE
4	4	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	06/08/2023		Notes 2	TRUE
5	34	Partner 2	Other	Company D	Other	Meeting	09/07/2023	1	TRUE	09/07/2023	x	Notes 3	FALSE
6	33	Partner 3	Other	Company E	Other	Action	09/07/2023	1	TRUE	09/07/2023		Notes 4	FALSE
7	35	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023		Notes 5	FALSE
8	42	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023	x	Notes 6	FALSE
9	46	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023		Notes 7	FALSE
10	47	Partner 1	Other	Company A	Other	Action	04/08/2023	1	TRUE	04/08/2023		Notes 8	TRUE
11	48	Partner 1	Other	Company B	Other	Action	06/08/2023	1	TRUE	06/08/2023	x	Notes 9	TRUE
12	206	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
Sheet10

Cell Formulas
Range	Formula
M3:M12	M3	=XLOOKUP([@Partner],check!$A$2#,check!$B$2:$B$4)

To Do.xlsm
	A	B
1	Partner
2	Partner 1
3	Partner 2
4	Partner 3
check

Cell Formulas
Range	Formula
A2:A4	A2	=SORT(UNIQUE(Table1[Partner]))
Dynamic array formulas.

To Do.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Partner
2	Partner 1
3	Partner 2
4	Partner 3
5
6
7	#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes
8	1	Partner 1	Other	Company A	Other	Meeting	31/01/2023	1	TRUE	02/02/2023	x	Notes 1
9	206	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10
10	4	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	06/08/2023		Notes 2
check

Cell Formulas
Range	Formula
A2:A4	A2	=SORT(UNIQUE(Table1[Partner]))
Dynamic array formulas.

 

[RoryA]

Assuming your table is actually called exampledata, not Table1, then I think this works:

=LET(
a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),
uc, UNIQUE(INDEX(a,0,4)),
b, SORT(SORT(a, 4, ), 7,-1 ),
c,INDEX(b,0,4),
result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),
result
)

 

[elcalvo]

Assuming your table is actually called exampledata, not Table1, then I think this works:

=LET(
a, FILTER(exampledata,(exampledata[Type] = "Meeting")*(exampledata[Include Partner])),
uc, UNIQUE(INDEX(a,0,4)),
b, SORT(SORT(a, 4, ), 7,-1 ),
c,INDEX(b,0,4),
result,REDUCE(exampledata[#Headers],uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),
result
)

thanks RoryA, this works ! I see now I kept including to VSTACKs ... and true, the table name was wrong

thanks a million