DOUBLE FILTER function to return ONLY records with most recent date when base data is sorted continuously

[elcalvo]

• I'm using Office 365
• I have a defined table named exampledata with headers and its data below
• In another sheet (called “check”), I have made a filter section for Partners in A2 with inserted checkboxes (insert – cell controls)
• The formula 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)
• The data in exampledata is continuously being sorted (on #, on Partner, on Company, on Prio) depending on the activity that I am doing. This is been done with a VBA code: doubleclicking on the header sorts the data in that column first (and then the rest of the data)

The output of the result table (the LET result) gives a different result when it comes to sorting in column 7 (“Last) depending on the exampledata sorting.



How to overcome this ?

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		Notes 1	TRUE
4	2	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	06/08/2023		Notes 2	TRUE
5	3	Partner 2	Other	Company D	Other	Meeting	09/07/2023	1	TRUE	09/07/2023		Notes 3	TRUE
6	4	Partner 3	Other	Company E	Other	Action	09/07/2023	1	TRUE	09/07/2023		Notes 4	TRUE
7	5	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023		Notes 5	TRUE
8	6	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023		Notes 6	TRUE
9	8	Partner 1	Other	Company A	Other	Action	04/08/2023	1	TRUE	04/08/2023		Notes 8	TRUE
10	7	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023		Notes 7	TRUE
11	9	Partner 1	Other	Company B	Other	Action	06/08/2023	1	TRUE	06/08/2023		Notes 9	TRUE
12	15	Partner 1	Other	Company B	Other	Meeting	06/08/2023	1	TRUE	06/08/2023		Notes 9	TRUE
13	14	Partner 1	Other	Company A	Other	Action	07/09/2023	1	TRUE	04/08/2023		Notes 8	TRUE
14	11	Partner 3	Other	Company A	Other	Meeting	30/11/2023	1	TRUE	04/08/2023		Notes 8	TRUE
15	10	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
16	16	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
17	12	Partner 3	Other	Company B	Other	Action	01/12/2023	1	TRUE	06/08/2023		Notes 9	TRUE
18	13	Partner 3	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
Sheet10

Cell Formulas
Range	Formula
M3:M8,M11	M3	=XLOOKUP(Sheet10!$B3,check!$A$2#,check!$B$2:$B$4)
M9	M9	=XLOOKUP(Sheet10!$B10,check!$A$2#,check!$B$2:$B$4)
M10,M14	M10	=XLOOKUP(Sheet10!$B9,check!$A$2#,check!$B$2:$B$4)
M12	M12	=XLOOKUP(Sheet10!$B17,check!$A$2#,check!$B$2:$B$4)
M13	M13	=XLOOKUP(Sheet10!$B16,check!$A$2#,check!$B$2:$B$4)
M15,M17:M18	M15	=XLOOKUP(Sheet10!$B12,check!$A$2#,check!$B$2:$B$4)
M16	M16	=XLOOKUP(Sheet10!$B18,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(Sheet10!$B$3:$B$18))
Dynamic array formulas.

To Do.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes	Include Partner
7	11	Partner 3	Other	Company A	Other	Meeting	30/11/2023	1	TRUE	04/08/2023	0	Notes 8	TRUE
8	6	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023	0	Notes 6	TRUE
9	5	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023	0	Notes 5	TRUE
10	7	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023	0	Notes 7	TRUE
11	15	Partner 1	Other	Company B	Other	Meeting	06/08/2023	1	TRUE	06/08/2023	0	Notes 9	TRUE
12	10	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023	0	Notes 10	TRUE
check

Cell Formulas
Range	Formula
A6:M12	A6	=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 )
Dynamic array formulas.

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		Notes 1	TRUE
4	2	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	06/08/2023		Notes 2	TRUE
5	3	Partner 2	Other	Company D	Other	Meeting	09/07/2023	1	TRUE	09/07/2023		Notes 3	TRUE
6	4	Partner 3	Other	Company E	Other	Action	09/07/2023	1	TRUE	09/07/2023		Notes 4	TRUE
7	5	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023		Notes 5	TRUE
8	6	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023		Notes 6	TRUE
9	7	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023		Notes 7	TRUE
10	8	Partner 1	Other	Company A	Other	Action	04/08/2023	1	TRUE	04/08/2023		Notes 8	TRUE
11	9	Partner 1	Other	Company B	Other	Action	06/08/2023	1	TRUE	06/08/2023		Notes 9	TRUE
12	10	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
13	11	Partner 3	Other	Company A	Other	Meeting	30/11/2023	1	TRUE	04/08/2023		Notes 8	TRUE
14	12	Partner 3	Other	Company B	Other	Action	01/12/2023	1	TRUE	06/08/2023		Notes 9	TRUE
15	13	Partner 3	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
16	14	Partner 1	Other	Company A	Other	Action	07/09/2023	1	TRUE	04/08/2023		Notes 8	TRUE
17	15	Partner 1	Other	Company B	Other	Meeting	06/08/2023	1	TRUE	06/08/2023		Notes 9	TRUE
18	16	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023		Notes 10	TRUE
Sheet10

Cell Formulas
Range	Formula
M3:M18	M3	=XLOOKUP(Sheet10!$B3,check!$A$2#,check!$B$2:$B$4)

To Do.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
6	#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes	Include Partner
7	11	Partner 3	Other	Company A	Other	Meeting	30/11/2023	1	TRUE	04/08/2023	0	Notes 8	TRUE
8	6	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023	0	Notes 6	TRUE
9	5	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023	0	Notes 5	TRUE
10	7	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023	0	Notes 7	TRUE
11	10	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	24/03/2023	0	Notes 10	TRUE
12	15	Partner 1	Other	Company B	Other	Meeting	06/08/2023	1	TRUE	06/08/2023	0	Notes 9	TRUE
check

Cell Formulas
Range	Formula
A6:M12	A6	=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 )
Dynamic array formulas.

 

[elcalvo]

How about

=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,DROP(REDUCE("",uc,LAMBDA(a,i,VSTACK(a,TAKE(FILTER(b,c=i),1)))),1),VSTACK(exampledata[#Headers],sort(result,7,-1)))

it keeps amazing me ... works as requested. Top ! Thanks again

Do I mark this as the solution ? (and override the previous)

 

[Fluff]

Do I mark this as the solution ?

No please keep the previous one, as that answered the original question. Thanks