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
