list clean-up · sample
1,240 customer rows from three places, reconciled to one list. Every row placed, every drop with its reason.
Brookside Lawn & Snow, a made-up crew of five. Their customer list lived in an old spreadsheet, the invoicing tool and the owner's phone. Three exports in, one list out, and the receipt.
In, and out
1,240rows in, three exports
→
1,102kept
96merged into a kept row
42dropped, each with a reason
1,102 + 96 + 42 = 1,240. Every row that came in is one of those three things, and the finished list says which on each row that was merged or dropped.
Why 96 merged
- Same email, different case or spacing"[email protected]" and "[email protected] " are one customer. The row with the newer last-job date was kept.61
- Same phone, different formatting"860-555-0117", "(860) 555 0117" and "8605550117". One number, one row.24
- Same name and street addressNo shared email or phone, but the same person at the same house. Both contacts kept on the one row.11
Why 42 dropped
- No way to reach themA name and nothing else. No email, no phone, no address. Listed in a side sheet in case the owner remembers.19
- Email that cannot be an emailMissing the @, or a domain with no dot. Ten of the eleven were typed on a phone.11
- Test rows"Test Test", "asdf", the owner's own number entered as a customer twice.7
- Outside the service areaFive one-off jobs in towns the crew no longer drives to, all before 2023. Kept in the side sheet, not the list.5
Schema check what each column was, what it is now
columncame in aswent out asrows changed
namethree columns across the exports: "Name", "First / Last", "Customer"one column, first and last, trimmed, consistent case212
emailmixed case, stray spaces, 11 impossible valueslower-case, trimmed; the 11 dropped with their reason340
phoneseven formats, some with "cell" typed after the numberone format, ten digits, one column for a second number480
townfree text, 38 spellings of 14 townsthe 14 towns, spelled one way each38
last jobfour date formats, 81 blankyear-month-day; the 81 left blank, not guessed1,021
do not calla "DNC" note buried in the notes column on 17 rowsits own yes/no column, the 17 carried over17
notesas typedas typed, untouched0
One merge, shown
export 1Nguyen, Pat · [email protected] · 860-555-0117 · Glastonbury · 11/3/2023
export 2Pat Nguyen · [email protected] · (860) 555 0117 cell · glastonbury · 2025-04-19
phonePat N · 8605550117 · — · — · —
keptPat Nguyen · [email protected] · 8605550117 · Glastonbury · 2025-04-19 · merged from 3 rows
The name, number and town are made up. The shape of the mess is not.
The receipt what was checked, not how
- 1,240rows in
- 1,240rows placed: kept, merged or dropped
- 0rows unexplained
- 1revision
- The three totals against the row count of each export, and against each other, before and after the merge.
- Every merged row carries the ids of the rows it came from; every dropped row sits in a side sheet with its reason. Nothing was deleted.
- Twenty kept rows: each matched to its source row.
- The one revision: the owner wanted the "do not call" flag as its own column, not a note. It is, all 17.
returned day 1 of 3 · one list, one side sheet, one revision
List clean-ups delivered to paying clients so far: 0. This page is the sample, and Brookside Lawn & Snow does not exist. The first real one gets a page like this with its own numbers.