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.

 

[Fluff]

The only difference I can see, is that the rows are in a different order, which is to be expected.
What exactly are you after?

 

[elcalvo]

the rows are in a different order, but I expected that it would always sort the dates in column 7 "Last" in the result table. This gives me the overview of the last meetings with the unique Company. If there is no way to keep the order in the result table from changing, I need to find a different solution

the exampledata is used as a worksheet and continuously filled and sorted; the result table is used as a quick overview without the need to apply more filters and actions and ideally giving the overview with the "Last" always sorted

 

[Fluff]

What order do you want it in? You can sort the data as part of the final formula

 

[elcalvo]

What order do you want it in? You can sort the data as part of the final formula

the date in "Last" descending

 

[Fluff]

In that case use

=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)))),sort(result,7,-1))

 

[elcalvo]

In that case use

=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)))),sort(result,7,-1))

absolutely fantastic ! thank you very much
works like a charm

 

[Fluff]

You're welcome & thanks for the feedback.

 

[elcalvo]

In that case use

=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)))),sort(result,7,-1))

@Fluff in case I want to make it descending and change the 1 to -1, also the headers get sorted which I don't want. What would be the fix for this ?

 

[Fluff]

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)))