Reconcile requested versus received administrative record categories Get the source data and prepare the review Manual source preparation required; native Excel reconciliation is documented; no case-management extraction integration verified WHAT WAS CHECKED Public official product documentation only. No product login, account write, real client data, installation, or live workflow test. Prompts and schemas are proposed editorial adaptations; vendor documentation verifies component features, not these end-to-end recipes. Native exports retain their real headers; kit schemas require the explicit mapping steps below. BEFORE YOU START - Excel desktop with Power Query (documented for supported Microsoft 365/Excel versions); optional firm-approved Copilot. - Actual approved request letters/log and receipt cover sheets or staff receipt log. An authorized staff member reviews them locally. No clinical records or patient identifiers go to the AI workspace. - Staff-defined request aliases, administrative category vocabulary and status meaning. The responsible lawyer determines evidentiary sufficiency separately. DATA ACQUISITION LIMITS - There is no universal Clio/MyCase/records-provider AI query or CSV export verified for this task. The first step is explicit manual recordkeeping. - A filename or cover-sheet label alone cannot prove the contents are complete. Staff_recorded_status is a human determination, not an AI clinical conclusion. STEP 1: Create the two small source tables from real records Tool: Local document viewer and Excel Open: Open the actual request and receipt cover sheets locally; Excel > blank workbook Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested. For each request, read the approved request’s administrative categories and assign a non-identifying request_id. In Expected, enter one row per requested category. In Receipts, staff enter one row per receipt/category after checking the receipt; assign receipt_id and copy their reviewed status. Exclude names, patient dates, treatment detail and raw records. Keep pointers to original sources only in a separate restricted index. Expected output: expected.csv: request_id,administrative_category,expected_flag. receipts.csv: receipt_id,request_id,administrative_category,staff_recorded_status. These are staff-created ledgers, not a product export. Use it next: Select each range > Home > Format as Table; name the tables Expected and Receipts. Check: Check category spelling and exact request aliases across tables. Missing evidence stays unknown. Do not infer a request ID from similar names. Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations. Source documentation: https://support.microsoft.com/en-us/excel/tutorial-import-data-into-excel-and-create-a-data-model STEP 2: Load the tables into Power Query Tool: Excel Power Query Open: Select a table cell > Data > From Table/Range Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested. Repeat for Expected and Receipts. Set request_id and administrative_category to Text consistently. Load/keep both queries; use their names to identify them. Expected output: Two queries containing the reviewed administrative rows. Use it next: Open Expected in the Power Query editor. Check: Check row counts and headers against the input tables; no external connector is required. Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations. Source documentation: https://support.microsoft.com/en-us/excel/import-data-from-data-sources-power-query STEP 3: Join expected categories to receipts Tool: Excel Power Query Open: Power Query editor > Home > Merge Queries > Merge Queries as New Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested. Choose Expected first and Receipts second. Select request_id then administrative_category in both previews in the same order. Choose Left outer explicitly and leave fuzzy matching off. Expand receipt_id and staff_recorded_status. Use Home > Close & Load for the result. Expected output: Every expected category retained with zero, one or multiple matched receipt rows. Null receipt data indicates no matching logged receipt. Use it next: Use the merged table for staff review or optional AI summary. Check: A duplicated match may be a valid partial shipment or duplicate log; do not collapse it without review. Filter expected_flag only according to the staff-defined rule. Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations. Source documentation: https://support.microsoft.com/en-us/excel/merge-queries-power-query https://support.microsoft.com/en-us/excel/import-data-from-data-sources-power-query STEP 4: Summarize exceptions without inventing completeness Tool: Excel Copilot, optional Open: Deidentified workbook > Copilot icon at lower right > Chat only Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested. Ask the prompt against the original two tables and merged result. Without Copilot, filter the result for null receipts, partial/unclear statuses and duplicate keys, then write the same checklist manually. PASTE THIS PROMPT Compare the reviewed Expected and Receipts tables by exact request_id and administrative_category, and check the merged result. List expected categories with no matching receipt, partial/unclear staff statuses, duplicate receipts and contradictory statuses, citing worksheet rows and receipt IDs. Return request_id, category, matched_receipt_ids and discrepancy. Preserve unknowns. Do not inspect clinical records, infer missing treatment, decide legal or medical sufficiency, invent receipt status, edit the workbook or send requests. Expected output: request_id,category,matched_receipt_ids,discrepancy and cited worksheet rows. Use it next: Staff open the original request/receipt locally to clear or explain each exception. Check: A missing BILLS row means no matching BILLS receipt in this log, not proof that medical evidence is inadequate. Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations. Source documentation: https://support.microsoft.com/en-us/excel/copilot/get-started-with-copilot-in-excel https://support.microsoft.com/en-us/excel/merge-queries-power-query STEP 5: Approve the administrative follow-up checklist Tool: Records staff and responsible lawyer Open: Reviewed reconciliation worksheet Access needed: See recipe prerequisites; exact firm subscription and account permissions not tested. Staff verify exceptions against original cover sheets and actual received contents under firm procedures. Correct the staff ledger only after checking; refresh the query when ready. The lawyer decides whether any substantive evidentiary gap exists. Expected output: Reviewed missing/partial/unclear administrative-category checklist. Use it next: Any new records request or external follow-up is a separately authorized action. Check: A clean join does not establish full record coverage; keep staff’s verification notes. Documentation limit: Linked official documentation supports the named feature/menu. The workflow-specific prompt, schema, mapping and reviewer rules are editorial adaptations. Source documentation: https://support.microsoft.com/en-us/excel/merge-queries-power-query ACCOUNT AND SOURCE CHECKS STILL NEEDED - The firm’s actual request/receipt storage product was not supplied, so its retrieval menus cannot be truthfully specified. - Power Query and Copilot availability vary by installed version/license; this route is intended for supported desktop Excel. USING THE FILES IN THIS KIT Use the exact files or source text named in the steps above. Native exports keep their own headers. The kit tables are editorial review worksheets; they are not a claim that a product exports those fields. Some routes use reviewed text directly or require additional original files. Never rename an unreviewed raw export to make it appear mapped. Keep expected-result files separate from inputs. The source account, actual data, prompts and end-to-end workflow were not tested. Any final communication, filing, permission change, payment or publication needs its own authorized human action. This guide performs none.