Getting a subset of data using several fields for criteria (Max Date)

[ScottC_00]

I have 30 or so fields of data.  The data represents audits done on a case.  Each case has a case number.  There are 2 kinds of audits Audit A and Audit B.  and each audit has an audit Date.  Each case may have zero, one or more than one audits for either Audit A or B or both.  Each audit has 8-12 questions, and each question is its own row.  So, each audit for each Case may have 8-12 rows with the same date.

I need to find the latest Audit A and the latest Audit B for each case number and keep those rows, dropping all other rows with earlier dates.  In the data attached, for case 123 I want to keep the Audit A rows that have a date of 4/15 and drop the rows for Audit A on 4/12 (keeping the MAX date).  For case 123 I want to keep the rows for Audit B that occurred on 5/20 since it’s the only one.

For Case 456 I want to keep the rows for Audit B that occurred on 7/21 and drop the ones from 5/10.  For case 456 there is no Audit A.

I tried doing this with a summary tool, but I want all the fields, including other fields not included in the aggregation or grouping.

I hope this makes sense.

Thanks

[gautiergodard · Accepted solution]

Hey @ScottC_00 

Please find solution attached using the a summary and join tool combination!

Hope this helps!

[ScottC_00]

Thanks gautiergodard.  Works perfectly.