Record-room data cleanup, duplicate cases and bulk notice printing
You are in charge of the record room of a district court that has migrated to a case management system. The pending civil cases have been exported to a spreadsheet with 4,860 rows and four columns: Case Number, Date of Institution, Status and Remarks. The Case Number column contains both old and new formats - 'O.S. 412/1998', 'OS/412/1998' and 'O.S.No.412 of 1998' - and 312 rows are duplicates created when old cases were renumbered. The Date of Institution column is partly text in dd-mm-yyyy, partly text in dd/mm/yyyy, and partly five-digit serial numbers because a regional setting was changed during export. The Remarks column carries free text, including 'sole respondent reported dead 14.06.2019'. The Principal District Judge wants a list of cases instituted before 1 April 2020 in which the sole respondent is recorded as dead, so that abatement can be considered, and 120 notice letters are to be printed from the same sheet for the heirs. Nothing may leave the court's network.
1. Describe how you would clean the date column and extract the required list, naming the tools and giving the working formula.
Read the worked answer
Mixed formats cannot be compared as text or read reliably by a direct lookup; they must first be converted to true dates. The safe route is Power Query (Data > From Table/Range), set the column locale to English (India) for the dd-mm and dd/mm variants and add a custom step to convert the five-digit serials with Date.From or Number.From followed by Date.AddDays from the 1899-12-30 base. If Power Query is unavailable, use Data > Text to Columns with DMY for the text block, then convert the serial block with a helper column =DATE(1899,12,30)+E2 and format as date.
With a clean Date column (say C) and Remarks in D, the extract is a helper key or a COUNTIFS on the cleaned data: =COUNTIFS($C$2:$C$4860,"<"&DATE(2020,4,1),$D$2:$D$4860,"dead"). The wildcard works only on text, so Remarks must be text and trimmed of stray spaces. The result is a count; the list itself is produced by filtering on the same two conditions or by an AutoFilter with date and text criteria. Every count must be reconciled against the unfiltered total before it is certified.
2. How should the 312 duplicate rows be handled without corrupting the record?
Read the worked answer
Blind use of Remove Duplicates is dangerous here, because the tool silently keeps the first row it meets - which may be the older duplicate carrying a stale status or a missing death remark - and returns a clean-looking figure that is wrong. Nor can a VLOOKUP be trusted against a key with duplicates: it will return the first match without warning.
First normalise the Case Number into a key column by stripping spaces, dots, slashes and the words 'of' and 'No.' so that all three formats collapse to one string. Then flag with =COUNTIF($E$2:$E$4860,E2)>1 and sort the flagged rows by the date of the last proceeding in descending order, so that when duplicates are removed the surviving row is the latest. Removal should be done on a copy of the sheet, with the removed rows pasted into a separate worksheet and a note of the row count (4,860 minus 312, if all 312 are true duplicates - this must be verified, since renumbering can also create genuine parallel entries). The Principal District Judge should sign off the cleaned file before it becomes the basis of any abatement list.
3. Set out the steps for printing the 120 heir notices from the sheet, and the safeguards for the data.
Read the worked answer
Save the cleaned sheet as a separate workbook containing only the columns needed - case number, parties, respondent's name, date of death and address - because a mail merge exposes every column in the data source to the merged document. In the word processor, insert MERGEFIELD codes for those columns, add an IF field so that a notice is generated only where the Remarks column carries the death entry, and use NEXT to step through records. Before printing, run the merge to a new document rather than directly to the printer, verify the record count reported by the merge against a COUNTIFS on the sheet, and read three sample notices against the source rows for spelling, designation and court name.
Safeguards: keep the file on the court's server, not on a personal drive or mail, and never send it through a personal email account; print to PDF and store the batch in the case-management folder with a date-stamped name; restrict write access to the record-room login; and retain the pre-cleanup export unaltered as the audit copy. Party addresses and death particulars are personal data and must not be shared with the courier in a machine-readable sheet.