Tagging of Patients

Reconciles the CAF line list, the voucher register and the charges sheet into one list of beneficiaries, tagging every row Pending, With voucher or Submitted — and flagging vouchers used outside the month they were claimed.

01The files

ALL PATIENTS

The DOH “CAF Form 1” line list — the PDF export, or the same table as a workbook.

WITH VOUCHERS

Every voucher issued this month, with its number and referral amount.

SUBMITTED

The charges workbook — proves which vouchers were actually used.

PREVIOUS REFERENCE — optional

Earlier months’ voucher registers — drag as many in at once as you like. They add no rows; they are what dates a voucher used this month but claimed in an earlier one.

How each row is tagged

Submitted: The voucher appears on the charges sheet, so it was issued and used. The amount is that charge line's total.
With voucher: A voucher was issued but no charge line exists for it, and the patient is on the line list. The amount is the referral amount.
Pending: Either a voucher with no charge line whose holder is not on the line list, or someone on the line list with no voucher at all.

Where the amounts can go wrong

Claimed and used are compared: The last column says “Same month”, or the month the voucher was claimed in — shaded red — when that is not the month it was used in.
Nothing is ever summed: A voucher billed on three charge lines stays three rows, and a patient holding two vouchers stays two rows — even when the voucher code is identical.
Truncated names are handled: The DOH export cuts long names off (“BAUTISTA, MA. ROSALIE ESPA”), so matching allows a truncated spelling and reordered name parts.
Uncertain matches wait for you: A name that only loosely resembles another is never applied on its own — it is flagged, and you say whether it is the same person.
Uncalculated totals are recovered: Where the charges sheet stores a formula with no saved result, the total is re-derived from the columns it adds rather than left blank.