Conditional Multiple Criteria Lookup to Find Rows to Keep

Dear fellow Excel Power Users:


I have spent over 9 hours today trying to solve this myself, and must conclude that I cannot do this alone. I would be greatly appreciative of anyone who can help me to resolve this issue.

I have a file (SEE ATTACHMENT example) in which I need to define which rows to discard and which to keep (which will eventually be automated with VBA).

• Each row represents a question answered in a test
• A user may have completed the test more than once
• Each "attempt" at the test is represented by the "Unique AID" number (or UAID)

My requirement / challenge is to find the appropriate UAID to keep, considering the following logic:

1. Return the UAID of the highest "Overall Score" of any attempts completed in the last 12 months,
2. ELSE, Return the UAID of the most recent attempt
3. Mark each row as "Keep" if returned via above logic, otherwise mark as "Delete"

Thanks again for any assistance you can provide!!!!!