UP Police Assistant Operator: Cleaning a 4,200-Row Beneficiary Workbook Before a 5 p.m. Deadline

Cleaning a 4,200-Row Beneficiary Workbook Before a 5 p.m. Deadline

Sanjay is a computer operator in a Block Development Office. His workbook NREGA_Beneficiaries.xlsx contains 4,200 data rows with the columns Sr No, Job Card No, Name, Father's Name, Village, Bank Account and Amount. Four problems must be solved before the 5 p.m. submission. (a) About 380 rows are duplicates, because three operators keyed the same muster rolls twice; in most cases the duplicated row has the same Job Card No and the same Amount, but in about 40 cases the same Job Card No appears with two different amounts. (b) In about 900 cells the 11-digit bank account number has lost its leading zero, and some cells display as 4.21E+10. (c) The supervisor wants the number of beneficiaries and the total amount for each village, printed as a separate page per village. (d) Sanjay's VLOOKUP pulling village names from a master sheet returns #N/A for 60 Job Card numbers that the master sheet definitely contains.

1. What should Sanjay do first before removing any duplicate row, and how should he handle the 40 cases where the same Job Card shows two different amounts?

Read the worked answer

Save a copy first. Before any deletion, Sanjay should keep the original as NREGA_Beneficiaries_BACKUP.xlsx (or copy the sheet to a tab named Raw). Deletion is irreversible once the file is saved and overwritten, and an audit of an NREGA payment list may be demanded later.

Then separate the two kinds of duplicate. They are not the same problem.

Identical duplicates (same Job Card and same Amount) are pure keying errors. Mark them with a helper column, e.g. =COUNTIFS($B$2:$B$4201,B2,$G$2:$G$4201,G2) in column H, then keep only the first occurrence and delete the rest. Data → Remove Duplicates on the selected columns works, but only after sorting, and it deletes silently - a helper column with conditional formatting is safer because it shows what will go.
Same Job Card, different amounts (about 40 rows) may mean two different muster periods, a wrong amount keyed in, or a genuine second payment. Deleting one at random would either under-report or over-report a payment. These rows should be flagged, filtered out and sent for verification against the muster roll and the bank statement, and the verified figure keyed in. Use =COUNTIF($B$2:$B$4201,B2)>1 combined with a distinct-amount check to isolate exactly these 40 rows.

Finally, after deletion, re-count: rows should equal 4,200 − duplicates, and the amount total should reconcile with the bank's total.

2. How should Sanjay restore and protect the bank account numbers, and why do some cells show 4.21E+10?

Read the worked answer

Why 4.21E+10 appears: this is only a display effect. The cell is in General format and the column is too narrow, so Excel switches to scientific notation. The stored value is still the full number. Two separate fixes are needed:

Widen the column and set the format to Number with 0 decimal places, or to a custom format 00000000000, which forces an 11-digit display.
Check whether the digits are actually intact. Excel stores only 15 significant digits. An 11-digit account number is well inside that limit, so the leading zero is the only thing lost, and a custom format or =TEXT(A2,"00000000000") restores it correctly. Sanjay should verify with =LEN(A2) - if any cell returns more than 15 characters, digits have been lost and that record must be re-entered from the bank master, not reformatted.

For the future: format the Bank Account column as Text before anyone types into it, or use Data → From Text/CSV and set the column type to Text during import. Typing an apostrophe before the number also works but is messy at scale.

Never paste the =TEXT(...) results over the original column without checking them - the result is text, and any later numeric comparison against the bank's file will fail. Keep the text version in a new column named Account_Text.

3. The VLOOKUP returns #N/A for 60 Job Card numbers that exist in the master sheet. List the likely causes in order of probability and the diagnostic check for each, plus how to build the per-village count and page-per-village printout.

Read the worked answer

Diagnose before rewriting the formula. #N/A means 'lookup value not found', so the question is why two apparently identical values fail to match.

1. Trailing or leading spaces (very common when three operators keyed data, or when pasting from a PDF). Check with =LEN(B2) on both sheets: if the lengths differ, spaces are present. Fix with =VLOOKUP(TRIM(B2),Master!$A$2:$C$9000,3,FALSE) or clean the data with Find & Replace for double spaces.
2. Text vs number mismatch. In the master the Job Card may be stored as a number, in Sanjay's sheet as text (often because it came from a text import). Check with =ISNUMBER(B2) on each sheet. Fix with =VALUE(B2) or =TEXT(B2,"0") to match the master.
3. Missing leading zeros or a truncated value - an 8-digit Job Card keyed where the master holds 10 digits.
4. Wrong fourth argument. TRUE (approximate match) on unsorted data produces wrong results and sometimes #N/A; it must be FALSE or 0.
5. Hidden characters copied from a website (non-breaking space) - TRIM will not remove those; use =CLEAN(TRIM(B2)).

Wrap the final formula in IFERROR(...,"CHECK") so that errors are visible as a worklist rather than as blank cells.

Per-village totals: an Insert → PivotTable with Village in Rows, Job Card No in Values set to Count (use Distinct Count via the Data Model if the same Job Card can repeat), and Amount in Values set to Sum.

One page per village: either filter the pivot and print each village separately, or sort by Village, use Data → Subtotal with 'Page break between groups' ticked, then print with Fit to 1 page wide.