Help finding the latest date with criteria

[asjmoron]

Hi All,

I am tyring to work out the best way to identify the latest date that has had actions on it in a list. Let me ellaborate...

kf_Client_Reference_Number	Audit_Date	Appointment_Date	Audit_Grade	Last Audit Date
26422	19/05/2018	04/10/2018	1	18/09/2019
26422	19/05/2019	18/09/2019	1
26422	19/05/2020	17/09/2020
26422	19/05/2021

The table above shows a list of audit dates from the past and future, I need to (preferabble by way of a forumla) find the last date that has an audit grade noted against it. As you can see in the last column I have manually added 18/09/2019. This would be the end result of the formula. If the result showed 17/09/2020 for example, this would be incorrect as there has not been a grade noted against the audit date.

Just for reference I have added another record to the table below so you can see how some of the data could come through....

kf_Client_Reference_Number	Audit_Date	Appointment_Date	Audit_Grade	Last Audit Date
26422	19/05/2018	04/10/2018	1	18/09/2019
26422	19/05/2019	18/09/2019	1
26422	19/05/2020	17/09/2020
26422	19/05/2021
26423	19/05/2018	04/10/2018	1	17/09/2020
26423	19/05/2019	18/09/2019
26423	19/05/2020	17/09/2020	1
26423	19/05/2021

I need to consider that this is only of around 10,000 reference numbers which can all have up to 10 lines per reference number.

Any help would be amazing!

 

[asjmoron]

ALSO... I forgot to mention that the grades are not always marked as 1. They can be 1, 2, 3, 0