132 against 118: reconciling ANC data before a district audit
You are the CHO at a PHC in Bayana block, Bharatpur. At the monthly review the block MCH officer points out that your sub-centre's HMIS-2 line shows 132 ANC registrations for August while the RCH portal shows 118, and 21 portal entries have a blank LMP field. ASHAs submit paper diaries at the end of each month; three of the six write only in Hindi and one has not submitted for two months. Your data entry operator resigned in July and you have been entering records yourself in a spreadsheet. The district audits data quality next week and wants a line-list of pregnant women with estimated delivery dates falling in the next 60 days. You have one desktop with spreadsheet software, intermittent broadband and a printer.
1. Explain the likely causes of the 132-versus-118 gap and describe how you would reconcile the two registers using specific spreadsheet operations.
Read the worked answer
A gap of 14 is almost never a single error. Expect four causes running together: the two-month-old missing diary (registrations existing on paper but never keyed), double counting where a woman registered at the sub-centre and again at a private clinic or after shifting village, entries keyed with different spellings of the same woman's name, and genuine late entry where the HMIS line was updated but the portal was not opened because broadband failed.
Reconcile by building one master line-list rather than comparing totals. Export the portal list to one sheet, type the HMIS line into a second sheet in the same column order (name, husband's name, village, LMP, EDD, phone). Add a helper column for a deduplication key such as name-and-village in lowercase. Use COUNTIF against that key to flag repeats, VLOOKUP or INDEX-MATCH from the portal sheet into the HMIS sheet to find entries present in one and absent in the other, and IFERROR wrapped around the lookups so blanks show as 'missing' instead of error codes. Filter for 'missing', verify each against the paper diary or a phone call, and only then correct the portal. Print the reconciled list and get it signed, so the correction is auditable.
2. Write the message you would send to the ASHAs asking for diaries by a deadline, with an English gloss, and explain why you worded it as you did.
Read the worked answer
Hindi message (short, phone-readable):
'सभी आशा दीदी को सूचना - इस महीने की डायरी और गर्भवती महिलाओं की सूची 5 तारीख तक अनिवार्य रूप से जमा करें। जिन महिलाओं की डिलीवरी अगले दो महीने में है, उनके नाम अलग पन्ने पर लिखें। डायरी में LMP की तारीख जरूर भरें - खाली छोड़ने से जिले की रिपोर्ट में गड़बड़ी आ रही है। देरी होने पर फोन करके बताएं।'
Gloss: 'Message to all ASHAs - submit this month's diary and the list of pregnant women by the 5th without fail. Write the names of women whose delivery falls in the next two months on a separate page. Always fill the LMP date - leaving it blank is causing errors in the district report. If you will be late, phone and tell me.'
Worded this way because: Hindi matches how three of the six already write, the deadline is a date and not 'soon', the LMP instruction names the specific defect causing the audit problem, and the closing line offers a phone route so a late ASHA reports rather than disappears - which is how the two-month gap happened.
3. How would you build the 60-day EDD line-list, and what would you do about the 21 blank LMP entries?
Read the worked answer
EDD is LMP plus 280 days, so the line-list is a date calculation, not a manual count. In the spreadsheet, add an EDD column with a formula such as =LMP+280 pointed at the LMP cell, format it as a date, then filter or count with something like =COUNTIFS(EDD_range,">="&TODAY(),EDD_range,"<="&TODAY()+60). Sort ascending by EDD so the most urgent appear first, print the list, and mark the columns the audit will ask for: name, village, ASHA, LMP, EDD, haemoglobin, high-risk flag, expected place of delivery.
The 21 blank LMP fields must not be faked to make the sheet look complete. Options in order of quality: retrieve the date from the ASHA diary or the woman's MCP card; ask the woman directly; and only if none exists, use the fundal height or first-trimester ultrasound to estimate gestation and record clearly that the EDD is estimated, with the method noted. An estimated EDD flagged as estimated is defensible in an audit; an invented LMP date is not, and it also corrupts your high-risk tracking, because wrong gestation means wrong timing for TT doses, IFA refills and delivery planning.