FILTER function to return ONLY records with most recent date

[elcalvo]

I have been playing around with the question that @craigexcel had and where @Fluff and @JamesCanale had their valuable input, I just cannot crack this nut.

• I'm using Office 365
• I have a sheet with headers and its data below. The data is named exampledata (headers in row 2; data in A3:L12
• In another sheet, 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)

In one attempt (on the original data, not the example data), it returned almost what I wanted but for the most recent dates. It gave at least unique values for the Company.

When trying to reproduce in an example, it returns only one line.

The formula I am using is:

=LET(
z, exampledata,
a, FILTER(z, INDEX(z, , 6) = "Meeting"),
b, SORT(SORT(a, 4, ), 7, ),
c, TAKE(DROP(b, , 7), , 1),
d, VSTACK(DROP(c, 1), 0),
e, c <> d,
result, FILTER(b, e),
result
)


Any suggestions as to how to adjust the formula to return the desired results? I appreciate your time at having a look at this.

Example data
#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes
1	Partner 1	Other	Company A	Other	Meeting	31/01/2023	1	TRUE	02/02/2023	x	Notes 1
4	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	24/03/2023		Notes 2
34	Partner 2	Other	Company D	Other	Meeting	09/07/2023	1	TRUE	09/07/2023	x	Notes 3
33	Partner 3	Other	Company E	Other	Action	09/07/2023	1	TRUE	09/07/2023		Notes 4
35	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023		Notes 5
42	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023	x	Notes 6
46	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023		Notes 7
47	Partner 1	Other	Company A	Other	Action	04/08/2023	1	TRUE	04/08/2023		Notes 8
48	Partner 1	Other	Company B	Other	Action	06/08/2023	1	TRUE	06/08/2023	x	Notes 9
206	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	06/08/2023		Notes 10
Expected result
#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes
4	Partner 1	Other	Company A	Other	Meeting	22/03/2023	1	TRUE	24/03/2023		Notes 2
35	Partner 2	Other	Company F	Other	Meeting	11/07/2023	1	TRUE	13/07/2023		Notes 5
42	Partner 2	Other	Company D	Other	Meeting	23/07/2023	1	TRUE	25/07/2023	x	Notes 6
46	Partner 3	Other	Company G	Other	Meeting	04/08/2023	1	TRUE	04/08/2023		Notes 7
206	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	06/08/2023		Notes 10
Current result
#	Partner	Class	Company	Product	Type	Last	Prio	X	Next	Due	Action/notes
206	Partner 1	Other	Company C	Other	Meeting	01/12/2023	1	TRUE	06/08/2023		Notes 10

 

[Fluff]

How about

=LET(f,SORT(FILTER(A3:L1000,F3:F1000="meeting"),10,-1),r,ROWS(f),SORT(FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(f,,4)=TOROW(INDEX(f,,4))),SEQUENCE(r,,,0))=1)))

 

[RoryA]

Or perhaps:

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

 

[elcalvo]

both work absolutely fine ! OMG, thanks a million

I will now go and see if I understood what you both did to learn from it

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Fluff]

Please do not mark as post as the solution, when it doesn't contain one. Thanks
You can mark either post#2 or 3 as the solution if you wish.