Removing Duplicate Records

The previous topic established how to calculate single cells correctly: reference type, conditional sum, boundary comparison, text trimming, day counting and lookup. Their shared limit was this: every formula looked at one cell, and no number that looks at the whole table was ever produced. Whether the data itself was clean was never asked either.

This lesson performs the first operation that looks at the whole table: removing duplicate records. The operation looks like a single command, but it contains at least three decisions. Which columns must match for two rows to count as the same. Whether invisible whitespace and letter case are ignored when text is compared. Which of the two rows counted as the same is the one that stays. All three move the reported number, and all three are unreadable from the table once the operation is done. These are the lesson’s two numbers: the remaining row count and how many decisions can be read back.

• SC1. The spreadsheet is modeled with node: a cell lives in a map, a formula is a small evaluator that carries its own text, and a range is a slice function. The dataset is also fictional — the 1,400 subscribers of the previous course, expanded into field visit records.
• SC2. Duplicates arise from two separate causes: the same subscriber and period pair being entered a second time, and a subscriber being read in two periods. The second is not a duplicate; if the key does not tell these two apart, the delete operation deletes data.
• SC3. The meter number is typed by hand during the field visit, so it may carry a leading space and lowercase letters. Text comparison depends on the trimming and letter case decision.
• SC4. The recoverability of a decision is measured by scanning the sheet: if the decision is written in a cell formula it is read in one step, if it sits in a separate note cell it takes two steps — first the note is found, then the column it belongs to is matched — and if it appears in no cell it is unreadable.

How Many Rows Remain When the Key Changes

// sheet.mjs — the spreadsheet is a MODEL: cells live in a map, a formula is a
// small evaluator, a range is a slice function. No application is imitated;
// tasks are named by what they do. The dataset is fictional.
export const SEED = 20260218, SUBSCRIBERS = 1400;
const mix = (x) => { x = Math.imul(x ^ (x >>> 16), 2246822507);
  x = Math.imul(x ^ (x >>> 13), 3266489909); return (x ^ (x >>> 16)) >>> 0; };
const rng = (t) => { let s = mix(t);
  return () => (s = (s * 1664525 + 1013904223) >>> 0) / 4294967296; };
const pick = (u, w) => { let t = 0;
  for (let i = 0; i < w.length; i++) { t += w[i]; if (u < t) return i; } return w.length - 1; };
export const REGION = [["north", "NR", 0.28, 21], ["south", "SH", 0.22, 17],
  ["east", "ET", 0.18, 26], ["west", "WT", 0.14, 14], ["central", "CT", 0.18, 23]]
  .map(([name, code, share, base]) => ({ name, code, share, base }));

// Spreadsheet model. A cell name is letter+number; a value is either a plain
// value or a formula. A formula carries its own text: the decision is written there.
const LETTER = (j) => String.fromCharCode(65 + j);
export const formula = (text, f) => ({ kind: "formula", text, f });
export class Table {
  constructor(header, rows) {
    this.header = header; this.cells = new Map(); this.n = rows.length;
    header.forEach((b, j) => this.cells.set(`${LETTER(j)}1`, b));
    rows.forEach((s, i) => header.forEach((b, j) =>
      this.cells.set(`${LETTER(j)}${i + 2}`, s[b])));
  }
  read(name) { const v = this.cells.get(name); return v && v.kind === "formula" ? v.f(this) : v; }
  write(name, v) { this.cells.set(name, v); return this; }
  letter(b) { return LETTER(this.header.indexOf(b)); }
  range(a) { const [s, b] = a.split(":");
    const j = s.charCodeAt(0) - 65, i0 = +s.slice(1), i1 = +b.slice(1);
    return Array.from({ length: i1 - i0 + 1 }, (_, k) => this.read(`${LETTER(j)}${i0 + k}`)); }
  column(b) { return this.range(`${this.letter(b)}2:${this.letter(b)}${this.n + 1}`); }
  rows() { return Array.from({ length: this.n }, (_, i) =>
    Object.fromEntries(this.header.map((b) => [b, this.read(`${this.letter(b)}${i + 2}`)]))); }
}

// Field visit records: one or two periods per subscriber, and some of them
// written twice because of manual entry.
const ROWS = [];
for (let i = 0; i < SUBSCRIBERS; i++) {
  const r = rng(SEED + i);
  const b = REGION[pick(r(), REGION.map((x) => x.share))];
  const base = Math.max(1.2, b.base * Math.exp((r() + r() + r() - 1.5) * 0.62));
  const meter = `${b.code}-${String(1000 + i).padStart(4, "0")}`;
  const periods = r() < 0.34 ? ["2026-01", "2026-02"] : ["2026-01"];
  for (const d of periods) {
    const t = Math.round(base * (d === "2026-02" ? 0.94 : 1) * 100) / 100;
    ROWS.push({ subscriber_no: 10001 + i, meter_no: meter, region: b.name, period: d,
      usage_m3: t });
    // Double entry: the same subscriber and period is written a second time. This
    // time the meter number may be entered with a space or in lowercase; the
    // reading may have been corrected.
    if (r() < 0.09) {
      const form = pick(r(), [0.34, 0.28, 0.38]);
      const correction = r() < 0.45;
      ROWS.push({ subscriber_no: 10001 + i,
        meter_no: form === 0 ? ` ${meter}` : form === 1 ? meter.toLowerCase() : meter,
        region: b.name, period: d,
        usage_m3: correction ? Math.round(t * 1.12 * 100) / 100 : t });
    }
  }
}
export const SHEET = new Table(
  ["subscriber_no", "meter_no", "region", "period", "usage_m3"], ROWS);

// Deduplication key: generates a key string from a row. When the key changes,
// a different number of rows remains from the same sheet.
export const CRITERIA = [
  ["C1 full row", (s) => Object.values(s).join("|")],
  ["C2 subscriber_no", (s) => `${s.subscriber_no}`],
  ["C3 subscriber_no + period", (s) => `${s.subscriber_no}|${s.period}`],
  ["C4 meter_no + period", (s) => `${s.meter_no}|${s.period}`],
  ["C5 meter_no (trimmed) + period",
    (s) => `${s.meter_no.trim().toUpperCase()}|${s.period}`],
];
export const dedupe = (rows, key, keep = "first") => {
  const g = new Map();
  for (const s of rows) { const a = key(s);
    if (!g.has(a) || keep === "last") g.set(a, s); }
  return [...g.values()];
};
export const sum = (a) => a.reduce((x, y) => x + y, 0);

if (import.meta.url === `file://${process.argv[1]}`) {
  const rows = SHEET.rows();
  console.log(`model (seed ${SEED}): ${SUBSCRIBERS} subscribers, sheet has ${SHEET.n} rows; ` +
    `raw total ${sum(SHEET.column("usage_m3")).toFixed(1)} m3`);
  const y = (h) => String(h[0]).padEnd(32) + h.slice(1).map((v) => String(v).padStart(11)).join("");
  console.log("\n" + y(["deduplication key", "remaining", "deleted", "total m3"]));
  const kept = {};
  for (const [name, f] of CRITERIA) {
    const l = dedupe(rows, f);
    kept[name.slice(0, 2)] = l;
    console.log(y([name, l.length, SHEET.n - l.length,
      sum(l.map((s) => s.usage_m3)).toFixed(1)]));
  }
  console.log(`\nthe loosest and the strictest criterion differ by ` +
    `${kept.C1.length - kept.C2.length} rows; the total shifts by ` +
    `${(sum(kept.C1.map((s) => s.usage_m3)) -
      sum(kept.C2.map((s) => s.usage_m3))).toFixed(1)} m3`);
}

model (seed 20260218): 1400 subscribers, sheet has 2037 rows; raw total 42645.6 m3

deduplication key                 remaining    deleted   total m3
C1 full row                            2005         32    41949.2
C2 subscriber_no                       1400        637    29828.2
C3 subscriber_no + period              1877        160    39147.7
C4 meter_no + period                   1965         72    40912.2
C5 meter_no (trimmed) + period         1877        160    39147.7

the loosest and the strictest criterion differ by 605 rows; the total shifts by 12121.0 m3

Five keys gave five numbers, and none of them produced an error. C1 full row is the key that looks safest and does the least work: it deletes only 32 of the 2,037 rows, because when a reading was corrected in a second entry the two rows are not identical. Here the caution leaves 128 rows of the problem exactly where they were.

C2 subscriber_no gives the cleanest result — 1,400 rows, exactly one row per subscriber — and brings the reported total down from 42,645.6 m³ to 29,828.2 m³. Most of the 637 deleted rows are not duplicates but the second period’s readings. Someone who looks at the sheet later sees 1,400 rows, confirms that the number is unique per subscriber, and cannot see what is missing: a wrong key does not produce an error message, it produces a tidy-looking table.

The remaining three numbers measure the text decision. C4 compares the raw meter number and leaves 1,965 rows; C5 applies the same key with text that is trimmed and brought to one letter case, and falls to 1,877. The 88 rows in between are the price of a single decision. That C3 and C5 give the same number does not show that the keys are equivalent: in this set they coincide because the meter number is unique per subscriber, and they would part ways once a household’s second meter is added.

Same Key, Two Keep Decisions

// keys.mjs — the same sheet, this time two questions: which row is kept in a
// duplicate group, and whether the decision can be read back from the sheet.
import { SHEET, CRITERIA, Table, formula, dedupe, sum } from "./sheet.mjs";
const rows = SHEET.rows();
const y = (w) => (h) => h.map((v, j) => (j ? String(v).padStart(w[j])
  : String(v).padEnd(w[0]))).join("");

// The same key, two keep decisions: the first entry or the last entry in the group.
const y1 = y([32, 10, 11, 12]);
console.log("same criterion, two keep decisions");
console.log(y1(["criterion", "keeps", "remaining", "total m3"]));
for (const [name, f] of CRITERIA.slice(2, 4)) {
  for (const keep of ["first", "last"]) {
    const l = dedupe(rows, f, keep);
    console.log(y1([name, keep, l.length, sum(l.map((s) => s.usage_m3)).toFixed(1)]));
  }
}
const i3 = dedupe(rows, CRITERIA[2][1], "first"), s3 = dedupe(rows, CRITERIA[2][1], "last");
console.log(`for C3 both decisions leave ${i3.length} rows, and the total differs by ` +
  `${(sum(s3.map((s) => s.usage_m3)) -
    sum(i3.map((s) => s.usage_m3))).toFixed(1)} m3`);

// The trail of a single subscriber: which key swallows which row.
const sample = rows.filter((s) => s.subscriber_no === 10029);
const y2 = y([12, 10, 8, 6, 6, 6, 6, 6]);
console.log(`\nsubscriber 10029 holds ${sample.length} rows in the sheet`);
console.log(y2(["meter_no", "period", "m3", ...CRITERIA.map((o) => o[0].slice(0, 2))]));
for (const s of sample) {
  console.log(y2([`"${s.meter_no}"`, s.period, s.usage_m3,
    ...CRITERIA.map(([, f]) => dedupe(sample, f).includes(s) ? "kept" : "-")]));
}

// Recoverability of a decision: one step if it is written in a formula, two
// steps if it sits in a separate note cell, unreadable if it is in no cell.
const CODE = ["D1 key columns", "D2 text trimming", "D3 retained row",
  "D4 deleted count"];
const readBack = (t) => CODE.map((k) => {
  const v = [...t.cells.values()], n = k.slice(0, 2);
  const f = v.some((x) => x && x.kind === "formula" && x.text.includes(n));
  const note = v.some((x) => typeof x === "string" && x.startsWith("decision ") && x.includes(n));
  return f ? 1 : note ? 2 : null;
});

const deleted = new Table(SHEET.header, i3);
const annotated = new Table(SHEET.header, i3)
  .write("G1", "decision D1 and D3: duplicates were removed by subscriber and period, first entry kept");
const key = (s) => `${s.subscriber_no}|${s.period}`;
const seen = new Map();
const traced = new Table([...SHEET.header, "unique_key", "group_order"],
  rows.map((s) => {
    const a = key(s); seen.set(a, (seen.get(a) ?? 0) + 1);
    const order = seen.get(a);
    return { ...s,
      unique_key: formula("D1 subscriber_no and period are joined; D2 meter_no " +
        "is trimmed and brought to one letter case",
        () => `${s.subscriber_no}|${s.period}|${s.meter_no.trim().toUpperCase()}`),
      group_order: formula("D3 the first entry in a group is kept, the later ones are flagged",
        () => order) };
  }));
traced.write("H2", formula("D4 a row whose group_order is above one is a duplicate",
  (t) => t.column("group_order").filter((v) => v > 1).length));

const y3 = y([22, 15, 17, 14]);
console.log("\ncan the decision be read back from the sheet");
console.log(y3(["decision", "deleted sheet", "annotated sheet", "traced sheet"]));
const result = [deleted, annotated, traced].map(readBack);
CODE.forEach((k, j) => console.log(y3([k,
  ...result.map((s) => (s[j] ? `${s[j]} ${s[j] === 1 ? "step" : "steps"}` : "unreadable"))])));
console.log(["deleted", "annotated", "traced"].map((name, j) =>
  `${name} ${result[j].filter(Boolean).length}/${CODE.length}`).join(", ") +
  " decisions can be read back");
console.log(`traced sheet holds ${traced.n} rows and ${traced.read("H2")} flagged rows; ` +
  `deleted sheet holds ${deleted.n} rows and does not say where ${SHEET.n - deleted.n} ` +
  `of the rows went`);

same criterion, two keep decisions
criterion                            keeps  remaining    total m3
C3 subscriber_no + period            first       1877     39147.7
C3 subscriber_no + period             last       1877     39347.8
C4 meter_no + period                 first       1965     40912.2
C4 meter_no + period                  last       1965     41023.3
for C3 both decisions leave 1877 rows, and the total differs by 200.1 m3

subscriber 10029 holds 3 rows in the sheet
meter_no        period      m3    C1    C2    C3    C4    C5
"NR-1028"      2026-01   12.74  kept  kept  kept  kept  kept
"nr-1028"      2026-01   14.27  kept     -     -  kept     -
"NR-1028"      2026-02   11.97  kept     -  kept  kept  kept

can the decision be read back from the sheet
decision                deleted sheet  annotated sheet  traced sheet
D1 key columns             unreadable          2 steps        1 step
D2 text trimming           unreadable       unreadable        1 step
D3 retained row            unreadable          2 steps        1 step
D4 deleted count           unreadable       unreadable        1 step
deleted 0/4, annotated 2/4, traced 4/4 decisions can be read back
traced sheet holds 2037 rows and 160 flagged rows; deleted sheet holds 1877 rows and does not say where 160 of the rows went

The first table shows the course’s measure plainly. Under key C3, the decision of which row is kept does not change the row count at all — 1,877 rows remain either way — but the totals pull 200.1 m³ apart. Someone looking at the sheet can verify the row count and find the right number; there is nothing to verify which keep decision produced the total. Row count is not an audit measure.

The second table traces the three rows of a single subscriber. Subscriber 10029’s January reading was entered twice, and in the second entry the meter number is in lowercase and the reading has drifted from 12.74 to 14.27; the third row is the February reading. Key C2 deletes two of the three rows, and of the deleted ones one is a real duplicate and one is the February reading. C4 deletes none, because the lowercase code counts as a different text. C3 and C5 do the right thing and leave two rows. Five keys give three different results for the same record, and what each one did can be seen only while the rows are still there.

The choice between 12.74 and 14.27 is a separate decision and has nothing to do with the key: the second entry may be a correction, or it may be a second erroneous entry. The data does not say which is the correct reading; the decision has to rest on information outside the sheet.

Where the Decision Lives

The third table is the lesson’s second number. The deleted sheet holds 1,877 tidy rows and carries zero of the four decisions. The table looks right, because nothing has been left that would make the table look wrong.

The annotated sheet gives back two of the four decisions, in two steps. The person who wrote the note wrote down the key columns and the keep decision but not the text trimming — they counted that as cleaning, not as a decision. Yet the measured difference is exactly there: trimming moves 88 rows, the keep decision zero rows. Notes kept by hand miss the quietest decision.

The traced sheet keeps all 2,037 rows, flags 160 of them and gives all four decisions in one step; because the decisions sit in cells as formulas, a second person who opens the sheet asks nobody in order to read them. The cost is in two places: the sheet does not shrink, and every later summary operation has to take the flag column into account. This cost is the price of the course’s rule — a spreadsheet is a calculation tool, not a record-keeping tool; a record can be bought only with an extra column.

Summary

• The deduplication key is a key generated from the row; on the same 2,037-row sheet five keys leave 2,005, 1,400, 1,877, 1,965 and 1,877 rows.
• A key based on the subscriber number alone produces the cleanest table and deletes the second period instead of the duplicates: the total falls from 42,645.6 m³ to 29,828.2 m³ and no warning appears.
• The text trimming and letter case decision alone moves 88 rows.
• Keeping the last entry instead of the first under the same key holds the row count fixed at 1,877 but pulls the totals 200.1 m³ apart; row count is not an audit measure.
• Once the operation is done, of the four decisions the deleted sheet gives back zero, the annotated sheet two in two steps, and the traced sheet four in one step.

Next Step

Deduplication assumed that all the remaining rows were filled in. Yet in some of the 1,877 rows the consumption cell is blank, in some it says zero, and the spreadsheet separates these two in some operations and not in others. The mean of the same column gives one number when blank cells are excluded, another when blanks are counted as zero, and a third when they are imputed with the column mean. The next lesson calculates these three decisions side by side and measures whether, once the imputation is done, it is still known which cell was blank to begin with.