PLUM Completion Audit Report

I have three weekly assignment files (8/4, 8/11, 8/18) showing what each Personal Lines Underwriting Manager (PLUM) was asked to review. Referral work is logged by Policy Number, Phone Call work by Item ID, and if something is swapped out the sheet lists a Replacement ID. A separate completion file logs what actually got done.

The job is to reconcile those two sources in a single Excel spreadsheet. Every assignment—whether identified by Policy #, Item ID, or Replacement ID—needs to be matched to its outcome so I can instantly see:

• Which PLUMs finished everything
• Which reviews ran Late, remained Incomplete, or were Replaced
• Counts by work type (Referral vs Phone Call) for both assignments and completions

Please build the workbook so the raw data is preserved on one tab and a clear, filterable summary sits on another. The summary must show completion status broken into my three categories (On time, Late, Incomplete) and allow me to drill down to the individual assignment line if I need detail. Basic conditional formatting or a pivot/dashboard that highlights trouble spots would be ideal, but keep the file lightweight and easy for me to refresh with new weeks.

I will supply the CSVs as soon as we start; you return a polished .xlsx file that meets the points above.