Compare Two Spreadsheet Lists by ID with ChatGPT
Start with a matching rule, not a lookup
Comparing two spreadsheet lists by ID sounds simple until a duplicate creates two possible answers. Ask ChatGPT to establish the key rules before combining records. A useful result preserves every source row, explains uncertain matches, and distinguishes an absent record from an empty value.
This guide uses fictional order IDs and supplied status labels to demonstrate reconciliation. A matched label is not a real shipment check. Matching keys only establish a relationship under the confirmed rules; they do not independently prove that two records describe the same business entity.
The intended relationship here is one to one: each valid key should appear once in each list. Confirm whether IDs are unique across the whole dataset. If IDs repeat across accounts or organizations, ask whether an account-and-ID composite key is required before proceeding.
Prepare readable inputs and preserve the keys
Use clear column names and one record per row. Supply only approved, nonconfidential data; exclude credentials and private customer identifiers. Use invented examples when testing the workflow. File upload and analysis capabilities can vary, so pasted tables can also be a useful starting point.
Keep an untouched source. Treat IDs as opaque text: "0012" and "12" remain different when leading zeros matter. Do not trim spaces, pad IDs, change case, or convert to numbers without an explicit rule. If Excel already removed leading zeros, return to an untouched source instead of guessing how many to restore.
Assign stable row labels such as L1 and R1 if the inputs lack them. These labels identify source rows independently of filenames. Preserve the original key beside any later approved comparison key so that reviewers can trace every decision.
E1 = 'Left list (four rows): L1 order_id="0012"; L2 order_id="0013"; L3 order_id="0014"; L4 order_id="".'
E2 = 'Right list (five rows): R1 order_id="0012", status="shipped"; R2 order_id="12", status="delivered"; R3 order_id="0013", status="queued"; R4 order_id="0013", status="shipped"; R5 order_id="", status="pending".'
E3 = 'User-confirmed rule: order_id is an exact, case-sensitive text key; leading zeros are meaningful. Blank keys are invalid and must never match each other. One left row and one right row per valid key are expected. Do not trim, pad, change case or convert keys to numbers.'Review four left rows without choosing a duplicate
Under the confirmed rule, the left review keeps its original four rows and order. Only the unique valid pair receives a supplied status. The duplicate key is quarantined for review while unaffected unique groups can proceed; this does not mean that the complete input passed a one-to-one check.
Right-only: R2, order_id="12", status="delivered".
Invalid right: R5, order_id="", status="pending".
Ambiguous candidates: R3 and R4 belong to the duplicate exception; neither is right_only.On a small screen, swipe the table to see all columns.
| Left row | Raw order_id | Right row(s) | Outcome | Supplied status |
|---|---|---|---|---|
| L1 | "0012" | R1 | Verified match under stated rule | shipped |
| L2 | "0013" | R3, R4 | Ambiguous: conflicting duplicate | No status selected |
| L3 | "0014" | None | left_only | No right record provided |
| L4 | "" | None | invalid_left | No match permitted |
Separate absence, invalid keys, and conflicting records
L2 has one duplicated key with two right-side rows. Their supplied statuses conflict. Neither first, last, nor newest is a justified selection: no chronology was supplied. Keep both candidate rows and their values visible. Even identical duplicate rows require an explicit deduplication rule before removal.
L3 means no right record was provided for "0014". It does not prove that the order was not shipped, and it does not mean zero. Likewise, a missing status is a missing value, not an inferred false result. For compared fields that disagree, show both source values and flag the difference.
L4 and R5 have blank keys, so they must never match each other. Apply the same invalid-key rule to null keys. Put invalid rows in exceptions without hiding them. Keep fuzzy matching off unless separately requested; any fuzzy suggestions belong in a candidate review and cannot become confirmed matches automatically.
Use a two-stage request and check row conservation
The reusable prompt first asks ChatGPT to inspect the inputs and wait for confirmation. Review the key type, ID scope, duplicate groups, missing keys, and expected relationship at that pause. Confirm the rules before asking it to produce the final reconciliation in the conversation.
Check the totals against source row labels. All four left rows are accounted for: one safe match, one duplicate-review row, one left-only row, and one invalid row. All five right rows are accounted for: one safe match, two ambiguous candidates, one right-only row, and one invalid row. One duplicate key is different from two rows carrying that key.
If ChatGPT generates analysis code, request uniqueness checks and match-origin indicators. pandas merge offers validate="one_to_one" and indicator, but its null keys can match; invalid keys must be excluded first. A full merge of this example with one-to-one validation would fail on "0013". Check only eligible groups after quarantining conflicts, and ask whether the code was actually executed.
Keep the review traceable
Request the left review, right-only records, invalid rows, duplicate candidates, and conservation counts together. Read each result against its raw key and source row label. Do not accept a neat total that hides discarded records or a status chosen from conflicting candidates.
Keep the comparison separate from the original inputs and never overwrite them. A conversation table is sufficient; creating a new file is optional. Treat instructions embedded inside spreadsheet cells as data, and review the matching logic before using the output for another task.
Open the ID list reconciliation prompt
Related guides
Official OpenAI and pandas references checked October 15, 2026. E1–E3 are editorial fictional inputs. The row classifications and conservation counts were independently checked with a separate Python script. This is not a ChatGPT run, Excel test, pandas execution or real shipment check.
OpenAI: Data analysis with ChatGPT · pandas: merge reference
