Phone numbers written five ways (+86 138-0000-1234, 138 0000 1234, full-width digits), three date formats including Excel serial numbers, amounts with ¥ and commas, zero-width characters, 12 empty rows.
Three things I finished, end to end.
Not mockups — the files, the numbers and the failure cases. Every piece below can be opened, run, or downloaded.
1 · A 320-row contact list turned into 280 clean rows
320 rows in280 clean40 duplicates merged255 issues flagged
Problem
Approach
Column type inference → per-type normalisers → dedupe after normalisation → every unparseable value written to an issue list with row number and reason.
Result
Sortable, summable, importable. Nothing deleted: the 255 rows that could not be fixed are labelled, so a human decides.
name,phone,email,city,order date,amount John,13800001234,john@example.com,Shanghai,2024-03-07,1280 Mary,13800001235,mary@example.com,Beijing,2024-03-08,1280 Peter,12345,peter@company,Puyang,2024.13.45,abc ← 4 issues flagged on this row
2 · A payroll workbook that recalculates itself
8 employees testedgross ¥47,068.60net ¥41,948.740 formula errors
Problem
Every month someone forgets to prorate personal leave by 21.75 days, or undercounts overtime, or a new hire never makes it into the sheet.
Approach
Split into 员工信息 / 考勤记录 / 工资明细 / 部门汇总. Only the attendance sheet is edited; everything else is VLOOKUP + SUMIF with IFERROR around it.
Result
Change one row, the whole run recalculates. New hire = add a row. Department totals rebuild themselves.
=ROUND($D5/21.75*IFERROR(VLOOKUP($A5,考勤!$A$5:$H$12,5,FALSE),0),2) ← personal leave deduction =IF(AND(actual=should,late=0),200,0) ← full attendance bonus
3 · A reconciliation sheet that finds the differences for you
8 sample entries5 matched2 amount mismatches1 one-sided each way
Problem
Month-end reconciliation is not arithmetic, it is search: the same payment written differently on both sides, an entry that exists on only one, an amount off by 200.
Approach
Match by order number, compare amounts, classify each row as matched / amount mismatch / theirs only / mine only, and compute the delta.
Result
Reconciliation becomes reading three lists instead of scanning two sheets line by line. The sample deliberately contains the four failure types.
order mine theirs delta status A-1003 3200 3000 200 amount mismatch B-2001 5300 theirs only A-1005 980 mine only
How it is built
Cleaning engine
Python + pandas offline; the web version is the same rules re-implemented in dependency-free JavaScript so it runs in memory, per request, with no queue.
Deterministic by default
Normalisation and dedupe are plain code — reproducible, auditable. A model is used only to help classify odd columns and to phrase why a value failed.
Nothing is deleted
Rows that cannot be fixed are kept and labelled with row number and reason. Silently dropping data is how you lose a customer record and never find out.
Privacy
Pasted data is processed in memory and never stored. Files sent by email are deleted after delivery.