Cleaning a 1,240-Row Cause-List Export Before Portal Upload
The High Court’s e-filing section exports a cause-list spreadsheet with 1,240 rows. The portal rejects the upload unless Case No is unique, Hearing Date is in yyyy-mm-dd format, and Bench is exactly one of 'I', 'II' or 'III'. The export contains Case No values such as 'HC/2025/0412' and 'HC/2025/0412 ' with a trailing space; dates appear as '14-06-2025' and '2025/06/15'; Bench entries include 'I', 'Bench I', 'bench-1', 'II' and 'Bench-2'; and 37 Priority cells are blank. The office uses Excel 2019 on Windows with English (India) locale. A junior assistant proposes to sort by Case No, delete duplicates by eye, and retype dates. The section head rejects that because the export runs every week and the same errors return. She wants helper columns, a repeatable cleanup, and a validation rule that blocks bad entries at the source.
1. Write an Excel formula, usable in a helper column, that flags every row whose Case No duplicates another row after trimming spaces. Explain why COUNTIF alone on the original column fails.
Read the worked answer
Use two helper columns rather than one ambiguous formula.
In B2, put =TRIM(A2) and fill down to B1241. This creates a clean comparison key.
In C2, put =COUNTIF($B$2:$B$1241,B2)>1 and fill down. TRUE means the cleaned Case No occurs more than once; FALSE means it is unique.
A single-column alternative is =SUMPRODUCT(--(TRIM($A$2:$A$1241)=TRIM(A2)))>1, but the helper-column version is easier for a junior assistant to audit.
COUNTIF on the original column fails because =COUNTIF($A$2:$A$1241,TRIM(A2)) trims only the criterion, not the values inside the range. A cell containing HC/2025/0412 still has a trailing space, so it is not equal to the trimmed criterion. The formula can therefore miss exactly the duplicates the office is trying to catch. Trimming the range through a helper column removes that blind spot.
2. Describe a formula-based conversion for both date patterns to yyyy-mm-dd, and state one check to prove the conversion worked.
Read the worked answer
Assuming the raw value is in A2, use:
=IF(ISNUMBER(SEARCH("/",A2)), DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))), DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))))
For 2025/06/15, the slash branch takes year 2025, month 06 and day 15. For 14-06-2025, the hyphen branch takes year 2025, month 06 and day 14. Format the result column as yyyy-mm-dd; the underlying value remains a real date, which the portal can validate.
Check: put =ISNUMBER(C2) in a test column and confirm it returns TRUE for every row, or count the converted column with =COUNT(C2:C1241) and confirm it equals 1,240. Also spot-check the earliest and latest dates against the source. Import the raw file as text or use Text to Columns with the correct date order; if Excel guesses month and day before the formula runs, 14-06-2025 may already be corrupted.
3. Recommend a data-validation and conditional-formatting setup for Bench and Priority that will survive next week’s export.
Read the worked answer
Bench control: select the Bench column, open Data > Data Validation > List, and use the source I,II,III. Set the error alert to Stop so a new entry such as Bench-2 cannot be typed. This prevents new bad values; it does not repair the existing ones, so first run a cleanup formula such as =IF(OR(TRIM(D2)="I",TRIM(D2)="II",TRIM(D2)="III"),TRIM(D2),"CHECK") or a lookup table that maps Bench I and bench-1 to I.
Priority control: apply Data Validation with the custom formula =LEN(TRIM(E2))>0 to block blanks, or a list if the office has defined priority codes.
Conditional formatting: select A2:E1241 and add a formula rule =$C2=TRUE to shade duplicate Case No rows. Add a second rule =ISBLANK($E2) to shade blank Priority cells. Keep these rules in a template workbook and paste each weekly export into the template, so the helper columns, validation and formatting are reused rather than rebuilt.