I85PREP
⌘ K
Karnataka VAO study materials
Karnataka VAO study resources

Cleaning 1,200 beneficiary rows and emailing them safely

Karnataka VAO · Put your knowledge to work in context.

All case studies

Cleaning 1,200 beneficiary rows and emailing them safely

The Panchayat Development Officer asks the Village Accountant to compile 1,200 beneficiaries from three sources - a spreadsheet kept in the Gram Panchayat office, a pen drive brought by an ASHA worker, and handwritten muster sheets - and email the list to the Block Development Officer by 5 p.m. The office has one desktop with the shared login 'GP-ADMIN', no UPS, and unreliable power. The spreadsheet has inconsistent spellings of the same names ('Lakshmi', 'Laxmi', 'Lakshmamma'), a visible Aadhaar number column, and a total row that does not match the number of rows below it. The pen drive also contains an .exe file. A staff member offers to send the file from his personal email because the office connection is slow.

1. List the steps and the specific spreadsheet operations you would use to clean and reconcile the list before sending it.

Read the worked answer
  1. Preserve the original. Copy the file to 2024-08-31_beneficiaries_raw.xlsx and work only on a working copy. A cleaning exercise you cannot undo is a data loss.
  2. Standardise names. Use TRIM to strip stray spaces and PROPER for case; then sort alphabetically so variants such as Lakshmi/Laxmi/Lakshmamma sit together and can be merged by eye with a note.
  3. Detect duplicates. Add a helper column =COUNTIF($B$2:$B$1201,B2); anything above 1 is a repeat. Then use Data > Remove Duplicates on name plus father's name plus village, not on name alone.
  4. Fix the Aadhaar column. Format as Text before entry so leading zeros and long numbers do not become scientific notation; validate 12 digits with =LEN(TRIM(D2)); run a duplicate check on Aadhaar, which catches more real duplicates than names.
  5. Reconcile the total. Use COUNTA on the ID column and compare with the printed total row; find the missing rows against the muster sheets and record each correction in a log.
  6. Freeze and protect the header row, and save with a dated filename before emailing.

2. How should the Aadhaar data and the email itself be handled? Address the .exe file and the personal-email suggestion.

Read the worked answer

Aadhaar: the number is personal and sensitive. In any list that leaves the office, mask the first eight digits and show only the last four (XXXX XXXX 1234), keeping the full number only in a protected register used for verification. Share on a need-to-know basis, do not paste it into the body of a mail, and never circulate it on WhatsApp groups.

Email: send from the official Panchayat/government email ID, not a personal Gmail account. A personal account is outside the department's record, cannot be produced in an audit or an RTI reply, and may breach the service rules on official communication; the slow connection is solved by compressing the file or sending it in parts, not by leaving the system. Address it to the BDO with a copy to the PDO only, use BCC where a group is unavoidable, write a clear subject line with the file name and date, and mention the row count in the body so the recipient can verify.

The pen drive: do not open the .exe file. Scan the drive with updated antivirus, copy only the required data file, and do not enable autorun. An executable from an unknown source on an office machine holding Aadhaar data is a ransomware risk, not a curiosity.

3. The shared 'GP-ADMIN' login, the missing UPS and the unaudited total row - what risk do they create together, and what would you fix first with no budget?

Read the worked answer

They combine into a single risk: an unattributable, unrecoverable data loss just before a deadline. The shared login means every entry carries the same identity, so no one can prove who changed a row; the total-row mismatch means the file is already internally inconsistent; a power cut during the save can corrupt it; and with no backup, the only copy dies with the machine.

Fix first, at zero cost:

  • Create individual user accounts with passwords and lock the screen when leaving the desk - accountability, no purchase needed.
  • Save a dated copy after every major step and copy it to an external drive or an approved e-office folder at the end of each day; keep one copy off the machine.
  • Write the correction log on paper as you go, so the work is reconstructable even if the file is lost.
  • Reconcile and freeze the totals before 5 p.m., not after.

With budget, the next purchase is a UPS, then a spare keyboard and a second backup drive. Until the totals reconcile and the file is backed up, the honest reply to the BDO is a status note, not a guessed number.