FILTER function to return ONLY records with most recent date

[craigexcel]

I'm using Office 365 @ work.
I'm working with some HR data, and have created a formula that returns a subset of the data, i.e. those employees with "Vacate" in the "Job Profile" field, since I want to keep a running list of 'vacated' employees on a year-to-date basis, instead of requesting some kind of ad hoc report from HR, which is already overwhelmed. I download the current week's source data every Monday (so always a Monday date in this field), and drop into an Excel table.

The formula shown is what I've come up with to get the subset of ONLY employees with the "Vacate" text. As can be seen in the sample data, there can be more than 1 record for a given employee, depending on when it became known the employee would be leaving. What I really need in the output, though, is only a single record for each employee in this subset group, for ONLY the most recent date in the "system rpt date" field, as shown in the 2nd set of data. (Note that while I left the MAX part of the formula in place, it doesn't do what I'm looking for. I left it in place since it doesn't seem to affect the output {tho not exactly what I'm looking for}, and as a prompt for me that MAX would likely be needed, just in a different way in the formula.) Also, have tried a few iterations of the "source!A2:A500000" to get that "system rpt date" data to be returned as 'm/d/yyyy', AND actually be a number as opposed to text, but haven't been successful. Still have had to manually format to display as 'm/d/yyyy'. Though I suppose leaving it as text may be ok, since I don't think there will be a need to use those values for further [numeric] lookups or anything like that.

It seems FILTER would be a key element of the formula, but with a number of iterations using FILTER, UNIQUE, INDEX / MATCH, SORT, I haven't hit on the right combination yet. The 1st set of data shows a generic sample of key fields I'm working with. The 2nd set shows what I'm looking for in the results.
I also just saw this post -- Filter in multiple areas -- where Joe4 suggested using VSTACK for a similar type of situation. Couldn't get further with that, since I'm still unable to figure out the MAX part (assuming that's what should be used) to return ONLY the most recent date for each employee.

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

=SORT(FILTER(CHOOSE({1,2,3,4,5,6},source!I2:I500000,source!B2:B500000,source!D2:D500000,source!C2:C500000,VALUE(TEXT(source!A2:A500000,"m/d/yyyy")),source!G2:G500000),(MAX(source!A2:A500000)*(ISNUMBER(SEARCH("Vacate",source!G2:G500000))))),{1,2},1,FALSE)

sample data subset, from FILTER function formula:

Company	employee ID	Last Name	First Name	system rpt date	Job Profile
Comp1	101​	Jones	Bob	45103​	job A (Position Vacate: 07/06/2023)
Comp1	101​	Jones	Bob	45110​	job A (Position Vacate: 07/06/2023)
Comp1	145​	Smith	Ron	45110​	job B (Position Vacate: 07/06/2023)
Comp1	152​	Wilson	Jack	45117​	job C (Position Vacate: 07/14/2023)
Comp2	136​	Anderson	Bill	45103​	job A (Position Vacate: 07/21/2023)
Comp2	188​	Smith	James	45124​	job D (Position Vacate: 07/21/2023)
Comp2	205​	Matson	Tom	45117​	job E (Position Vacate: 07/19/2023)
Comp2	205​	Matson	Tom	45124​	job E (Position Vacate: 07/19/2023)

expected results:

Comp1	101​	Jones	Bob	45110​	job A (Position Vacate: 07/06/2023)
Comp1	145​	Smith	Ron	45110​	job B (Position Vacate: 07/06/2023)
Comp1	152​	Wilson	Jack	45117​	job C (Position Vacate: 07/14/2023)
Comp2	136​	Anderson	Bill	45103​	job A (Position Vacate: 07/21/2023)
Comp2	188​	Smith	James	45124​	job D (Position Vacate: 07/21/2023)
Comp2	205​	Matson	Tom	45124​	job E (Position Vacate: 07/19/2023)

 

[JamesCanale]

Here, I am making sure that your input filtered data (z in my thing) is sorted by ID and then by date. It may be that it is already sorted like that, so the formula could be simplified further.

MrExcelPlayground19.xlsx
	B	C	D	E	F	G
1	Company	employee ID	Last Name	First Name	system rpt date	Job Profile
2	Comp1	101	Jones	Bob	6/26/2023	job A (Position Vacate: 07/06/2023)
3	Comp1	101	Jones	Bob	7/3/2023	job A (Position Vacate: 07/06/2023)
4	Comp1	145	Smith	Ron	7/3/2023	job B (Position Vacate: 07/06/2023)
5	Comp1	152	Wilson	Jack	7/10/2023	job C (Position Vacate: 07/14/2023)
6	Comp2	136	Anderson	Bill	6/26/2023	job A (Position Vacate: 07/21/2023)
7	Comp2	188	Smith	James	7/17/2023	job D (Position Vacate: 07/21/2023)
8	Comp2	205	Matson	Tom	7/10/2023	job E (Position Vacate: 07/19/2023)
9	Comp2	205	Matson	Tom	7/17/2023	job E (Position Vacate: 07/19/2023)
10
11
12	Results
13	Comp1	101	Jones	Bob	7/3/2023	job A (Position Vacate: 07/06/2023)
14	Comp2	136	Anderson	Bill	6/26/2023	job A (Position Vacate: 07/21/2023)
15	Comp1	145	Smith	Ron	7/3/2023	job B (Position Vacate: 07/06/2023)
16	Comp1	152	Wilson	Jack	7/10/2023	job C (Position Vacate: 07/14/2023)
17	Comp2	188	Smith	James	7/17/2023	job D (Position Vacate: 07/21/2023)
18	Comp2	205	Matson	Tom	7/17/2023	job E (Position Vacate: 07/19/2023)
Sheet9

Cell Formulas
Range	Formula
B13:G18	B13	=LET(z,B2:G9,a,SORT(SORT(z,5),2),b,TAKE(DROP(a,,1),,1),c,VSTACK(DROP(b,1),0),d,b<>c,FILTER(a,d))
Dynamic array formulas.

 

[craigexcel]

@JamesCanale - Thanks for your reply. I haven't had a chance to apply that to my data yet, but I do appreciate your reply. I'm even less familiar with LET and TAKE and DROP, but it looks like this will give me some good practical experience with better understanding all 3.

 

[Fluff]

What version of Excel are you using as your profile says 2016, which doesn't have some of those functions?

If you have 365 another option is

=LET(f,SORT(FILTER(CHOOSECOLS(source!A2:I500000,9,2,4,3,1,7),ISNUMBER(SEARCH("vacate",source!G2:G500000))),{1,2,5}),r,ROWS(f),FILTER(f,MMULT((SEQUENCE(r)>=SEQUENCE(,r))*(INDEX(f,,2)=TOROW(INDEX(f,,2))),SEQUENCE(r,,,0))=1))

 

[craigexcel]

I have Office 365 @ work, and 2016 @ home. This is a work challenge, so Office 365 is the relevant version. Thanks for your suggestion. I'll have to try that one out too.

 

[craigexcel]

Finally circling back to your suggested formulas again. Thanks a lot for taking the time to create formulas. Both work exactly as I was intending.
And now I have at least a better understanding of the LET, TAKE, and DROP functions.

 

[Fluff]

Glad we could help & thanks for the feedback.