Match requested record categories to receipts

Practice relevance

Cross-practice idea · Useful supporting administrative method; no claim of practice-specific legal instructions.

Scope: Excel/Power Query administrative request-category comparison; not a statutory disclosure completeness determination.

The problem

Compare administrative request categories with received-item metadata.

Why it matters: A receipt can cover one requested category while another is still outstanding. A category-by-category check gives staff a clear collection list without deciding whether the evidence is legally sufficient.

Sources & evidence

Proposed / not tested. The public source describes the problem. Documentation supports only the stated method components; no result from this recipe has been verified.

What’s been verified

Source methods and reported problems have been reviewed. We have not run the adapted workflow. The sample below describes expected behavior on synthetic data, not a customer result.

What to measure: In a synthetic pilot, count correctly found exceptions, false alarms, and unresolved inputs. No time or cost saving is assumed.

Pain evidence: Records requests ↗Records requests, 2026-07-20, older. OP is asked to check whether all records and bills are present and fears being blamed for omissions; comments distinguish records received without corresponding billing.
Method: Power Query merge ↗Power Query merge provides deterministic table matching. The proposed completeness report is our design, not a documented medical feature.

Source checked: October 15, 2026. Pain accounts are self-reported. Documentation supports components; this adapted recipe and prompt have not been tested. We wrote these prompts. The adapted workflow hasn’t been tested.

Suggested approach

Start when: Staff records a receipt

compare an attorney/staff-approved request manifest with receipt metadata
Keep source references and report unknowns instead of filling gaps.
The responsible reviewer checks the report before any separately approved action.

Limits and review

The assistant drafts or reports only. Check source records and review the output before a human performs any separately approved send or change. No automatic legal decision, money movement, account connection or publication is included.

Who reviews it: Staff determine whether received material actually meets the authorized request; attorney determines evidentiary sufficiency. Test a records receipt with absent billing and a duplicate receipt.

What you need

Excel Copilot, optional · prepared inputs

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.
Use synthetic inputs first. Keep real data in firm-approved accounts and processing, with appropriate access, confidentiality and retention controls. Aliases alone do not anonymize records. Never paste credentials, API keys or unnecessary client details.

How to use

Manual source preparation required; native Excel reconciliation is documented; no case-management extraction integration verified

Before you start

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.

Check access and setup

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.

Create the two small source tables from real records
Local document viewer and Excel · Open the actual request and receipt cover sheets locally; Excel > blank workbook
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.
Next step
Select each range > Home > Format as Table; name the tables Expected and Receipts.
Check before continuing
Check category spelling and exact request aliases across tables. Missing evidence stays unknown. Do not infer a request ID from similar names.
Step documentation 1 ↗
Load the tables into Power Query
Excel Power Query · Select a table cell > Data > From Table/Range
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.
Next step
Open Expected in the Power Query editor.
Check before continuing
Check row counts and headers against the input tables; no external connector is required.
Step documentation 1 ↗
Join expected categories to receipts
Excel Power Query · Power Query editor > Home > Merge Queries > Merge Queries as New
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.
Next step
Use the merged table for staff review or optional AI summary.
Check before continuing
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.
Step documentation 1 ↗Step documentation 2 ↗
Summarize exceptions without inventing completeness
Excel Copilot, optional · Deidentified workbook > Copilot icon at lower right > Chat only
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.
Excel Copilot, optional promptCompare 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.
Next step
Staff open the original request/receipt locally to clear or explain each exception.
Check before continuing
A missing BILLS row means no matching BILLS receipt in this log, not proof that medical evidence is inadequate.
Step documentation 1 ↗Step documentation 2 ↗
Approve the administrative follow-up checklist
Final human review
Records staff and responsible lawyer · Reviewed reconciliation worksheet
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.
Next step
Any new records request or external follow-up is a separately authorized action.
Check before continuing
A clean join does not establish full record coverage; keep staff’s verification notes.
Step documentation 1 ↗

What you’ll get

Excel/Power Query or approved assistant workspace; output receipt-reconciliation.csv with request_id, category, matched_receipt_ids, discrepancy.

Inputs and a sample test Synthetic example · not a measured result

Input files

expected.csv: request_id, administrative_category, expected_flag. receipts.csv: receipt_id, request_id, administrative_category, staff_recorded_status. No records themselves or clinical data.

Synthetic example

REQ2 expects categories RECORDS and BILLS; one RECORDS receipt exists → BILLS has no matching receipt, not a conclusion that medical evidence is incomplete.

Expected output

REQ2 expects categories RECORDS and BILLS; one RECORDS receipt exists → BILLS has no matching receipt, not a conclusion that medical evidence is incomplete.

Test a failure

Staff determine whether received material actually meets the authorized request; attorney determines evidentiary sufficiency. Test a records receipt with absent billing and a duplicate receipt.

Try with the sample files

Follow the tool steps above and use the inputs named in each step. Where provided, native-* files are blank scaffolds and NATIVE-INPUT-MAPPING explains their fields. Original examples and the optional full review prompt use a separate prepared-input schema. Keep them separate unless the mapping explicitly connects them. Downloading a kit does not connect accounts or prove the workflow works.

Download inputs and checklist 8 files

Instructions, blank templates and separate synthetic examples; these files do not install an automation. Blank templates still need verified source data.

Download starter kit All inputs, synthetic examples and review notes in one ZIP. No automation is installed.
Start here: get the sources and prepare the review Documentation-checked acquisition, explicit manual mapping, exact prompts and human review limits
Setup and review checklist Required inputs, file use, review steps and limits
Expected blank CSV Blank input headers for expected.csv
Expected synthetic example Synthetic test records for expected.csv
Receipts blank CSV Blank input headers for receipts.csv
Receipts synthetic example Synthetic test records for receipts.csv
Synthetic example expected results Compare a pilot result with the planted exceptions

Optional full review prompt for prepared inputs

Prepared-input review prompt with Excel Copilot, optional · prepared inputs

Compare these deidentified request-manifest and receipt-status tables using request ID and category. Report expected categories with no matching receipt, partial receipts and contradictory status. Cite rows. Do not decide whether medical evidence is adequate, infer missing treatment or inspect clinical records. Produce a staff-review checklist only.

INPUTS
expected.csv: request_id, administrative_category, expected_flag. receipts.csv: receipt_id, request_id, administrative_category, staff_recorded_status. No records themselves or clinical data.

OUTPUT
Excel/Power Query or approved assistant workspace; output receipt-reconciliation.csv with request_id, category, matched_receipt_ids, discrepancy.

Use only these synthetic or firm-approved inputs and preserve source row/page IDs. Keep missing or conflicting evidence unresolved. Return a draft for the named reviewer. Do not send messages, connect accounts, change records or permissions, move money, or make legal decisions. Keep source files unchanged. List missing prerequisites before continuing.

Proposed / not tested · editorial prompt. This prompt uses the original prepared-input schema shown in the sample test. Step prompts may use different tables. Follow NATIVE-INPUT-MAPPING where supplied; do not mix the original examples with native-step inputs without mapping and checking every field.

Read original method Read pain evidence