Merge same-name staff lists by ID: a complete matching and review exercise

Establish where identity evidence comes from

Editorial advice: define the key before merging. Does a staff ID represent the same identity in both systems? Can it be reused? Do leading zeros matter? Shared names, similar departments and matching row positions do not answer these questions. Specify allowed normalization separately rather than letting AI remove characters or invent identifiers.

RFC 8259 distinguishes JSON strings from numbers and recommends unique object member names. That is a representation rule, not identity verification. Using a person’s name as an object key makes shared names difficult to retain. An array of records with string identifiers can represent both entries. Review the CSV and JSON identifier checks when converting.

Four inputs require three different decisions

The directory, identifiers and training entries below are original fictional examples without real personal data. Assume an authorized owner has confirmed that staff_id means the same thing in both systems. Missing IDs cannot be inferred from names. R01 and R02 share a name but have distinct IDs; T01 has an abbreviated name and the same ID as R01.

Expected decisions: link T01 to R01, keep R02 without a training match, and mark T02 unresolved. This does not establish absence from training or identify which Lin Chen T02 belongs to.

Download both original tables before joining

The exercise ZIP provides directory.en.csv and training.en.csv, Chinese counterparts, expected-links.csv and review-ledger.csv. Start with the two inputs for one language only. The answer ledger is editorial reference material; it is not another input table or generated AI output.

Keep source_row as evidence. Both inputs use the same column names but hold different kinds of record. Do not click “Merge all original tables”: that appends rows and cannot establish a staff-to-training relationship.

source_row,staff_id,name,department
R01,E007,Lin Chen,Design
R02,E021,Lin Chen,Operations

source_row,staff_id,name,course
T01,E007,L. Chen,Intro
T02,,Lin Chen,Intro

Request decisions instead of only a shorter list

Supply the following rule with all four rows to an authorized AI service. Use fictional data for a pilot and follow organizational rules for real lists. A prompt describes constraints without guaranteeing compliance, so review the output row by row.

1. 1
   Save the original four inputs with stable source-row labels, preserving missing values and names.
2. 2
   Confirm the identifier systems and allowed normalization manually. This example permits exact ID matching only.
3. 3
   Ask for per-source decisions and reasons, separating missing or conflicting identifiers as unresolved.
4. 4
   Check that T01 is linked because of E007, not name similarity, and that T02 is not silently assigned.

Treat each source row as evidence. Match records only by the supplied staff_id, whose shared meaning has been confirmed. Keep IDs as strings. Do not match by name, row order or department.
Return: source row IDs, decision, matched row IDs, reason, unresolved question.
Allowed decisions: matched, unmatched, unresolved.
Missing or conflicting IDs must remain unresolved. Keep every source row traceable.
For R01/R02/T01/T02, explain which rows can be linked and which require confirmation.

Run the full join and inspect source-side fields

Open Data cleaning and analysis. This operation compares the two selected original tables, not an accepted cleaning result. The current join trims surrounding whitespace from keys; these fixtures contain none. If such trimming is forbidden by your identifier rules, use a matching route that preserves those distinctions.

1. 1
   Import directory.en.csv and training.en.csv together, then expand “Join and compare (original tables)”. Select the directory as Left table and training as Right table; verify the file names rather than trusting import order.
2. 2
   Set Comparison key to staff_id and Join type to Full join. Click Join by key. Keep both source_row columns, both staff_id columns and both original names.
3. 3
   Expect three rows: R01–T01, R02–blank and blank–T02. A left join would omit the unmatched right-side T02; if you choose that route, retain its separate unresolved ledger.
4. 4
   Download CSV and reopen it. Read source-side headers to distinguish directory source_row from training source_row. Reconcile against expected-links.csv by source pairs, without relying on output row order.

The tool performs key matching. It does not verify the shared ID system, decide attendance or automatically create the review decision ledger.

Check source coverage rather than plausible headcounts

Three rows can still be three wrong relationships

In a fresh run, deliberately change the key from staff_id to name while retaining Full join. R01 and R02 now both match T02 because the names are equal; T01 remains unmatched because L. Chen differs. The result still contains three rows. This is a constructed counterexample, not a model benchmark.

Compare source pairs: the correct result has R01–T01 once, whereas the fault repeats T02 twice. Reject the name-based output, restore staff_id, rerun and download again. Do not repair by deleting one repeated-name row: that would discard an independent staff ID.

Handle missing IDs, aliases and conflicts differently

Resolve a missing ID with an authorized owner or source system rather than asking the model to guess. For the same ID with conflicting fields, preserve both values and versions. Longer text is not necessarily newer. Different identifier systems require a verified mapping before cross-system matching.

Do not turn empty fields into zero or “none.” The missing-versus-zero extraction guide distinguishes unavailable information, unresolved status and explicit absence. A name can help a human find candidates without automatically becoming merge evidence.

Deliver matches that can return to their inputs

Keep original sources, rules, output and manual confirmation records. Before calculating totals, define whether you are counting people, source rows or confirmed matches. Four input rows do not imply four people, and an unresolved entry does not establish a complete headcount. After a small sample passes, review duplicate IDs and coverage in the real material.