Looking for formula or script to return multiple applicants

[rv02]

HI

I appreciate if someone can advise me on solving the following problem.
I have attached a sample spreadsheet to demonstrate what I need to achieve.

From the attached workbook what I need to do is whether to have a formula/ or script to do the following

Look at each IDs in column A and see if the status is Fail. What I need to do is then to look at that ID and see if that person has applied again in the following years and if so what was the result has been.
Eventually, I need to return the IDs of the people who have applied and failed but have passed by replying in subsequent years.

To demonstrate for example in the attached workbook ID AA failed in 2019,2020,2021 but passed in 2022 so I need to return ID AA and the year they applied and failed and the year they eventually passed preferably in a separate sheet to have the columns id, year and status. Pass to be the last entry for each ID.
I also need another sheet to show any multiple applicants and the year of applications and statuses only for duplicated IDs who have always failed.
I need to ignore anyone who has only applied once during all the years.

I hope I am being clear on what I need to achieve, whether by VB script, Pivot pr formula

[Fluff13]

How about for pass

Please Login or Register  to view this content.

and for fail

Please Login or Register  to view this content.

[rv02]

This works perfectly. Thank you so much

[Fluff13]

Glad to help & thanks for the feedback.