Build a Reconciliation Workbook from Two ID Lists

You have two device-ID exports and need the actual records that appear in both lists, only in the first, or only in the second. A useful reconciliation gives you those rows, retains the originals and explains what happens to duplicates and empty IDs.

This worked example includes a finished workbook, original typed inputs, complete results CSV and a complete request you can reuse. The device lists are original fictional teaching data. They do not describe customer devices or establish inventory status.

Start with the comparison you need

The question here is: Does each source row's exact ID appear at least once in the other list? Every original row receives a result. Two A rows can both be Matched because one equal ID exists in B. That does not allocate the B row twice or claim that two physical devices were paired.

If your task is to allocate individual records one to one, you need a different rule and enough identifying fields to choose counterparts. These inputs contain IDs alone. This example deliberately performs membership comparison without inventing a pairing.

Preserve the two original lists

Each list has ten rows. Stable source rowIDs identify records even when the device ID is repeated or missing. The notation below uses JSON-style quotes: "Q-7 " includes a space before the closing quote. <blank> means an actually empty source ID cell. The quotes and <blank> marker are display notation, not characters stored in the raw IDs.

RawA

RawB

The A CSV and B CSV contain the same records. CSV quotation alone does not establish a spreadsheet column's data type. If you import these files, keep the ID column as text and verify 0012 before comparing. Do not reopen a CSV and assume a numeric-looking ID survived conversion.

In the workbook, RawA and RawB preserve the source records in A5:B14. Column A holds rowIDs and column B holds text IDs or empty cells. Column C is an ID-literal preview. It makes "0012" and "Q-7 " unambiguous without editing the original cells.

Write the rules before building the result

One rule remains open: Should A008 "Q-7 " and B007 "Q-7" represent the same identity? The source material does not answer this. The workbook reports them as Left-only and Right-only under exact equality, and flags both for review. Those are provisional exact-string results, not a decision that the underlying devices differ.

The supplied case rule already settles ABC versus abc. The leading-zero rule already settles 0012 versus 12. They are not additional unresolved questions.

Use the workbook

1. Open Results. The summary is above the complete 20-row result. Each line carries its source rowID, so an ID is never used as the sole record locator.
2. Use the Classification column to extract Matched, Left-only, Right-only or Blank ID rows. correct-results.csv contains the complete raw-ID version of this result.
3. Open Exceptions and filter Attention to exclude empty entries. The view retains all source rows so its formulas can react to input edits. Eight rows are flagged in this example: two blank rows, four rows participating in duplicate groups and the two whitespace candidates.
4. Resolve the specific whitespace question with the person responsible for the identifier scheme. Record the answer in Results B42. This field records a decision only. Selecting Confirm alias does not rewrite IDs or automatically change the matching policy.

If the answer is Keep distinct, keep the exact comparison and close that review with its recorded reason. If the answer is Confirm alias, preserve RawA and RawB, define the approved prepared-key or alias mapping separately, and regenerate the comparison and totals. Do not silently edit the original IDs. This delivered workbook has no alias mapping and leaves the decision Unresolved.

Follow one source row through the calculation

Results row 15 represents A001. It links to RawA A5 for its source rowID and to RawA B5 for the ID. Its displayed ID literal is calculated with:

=IF(LEN('RawA'!B5)=0,"",CHAR(34)&'RawA'!B5&CHAR(34))

The LEN check preserves 0 as a nonblank ID. Quotation marks belong only to this display cell. Counting formulas read the original text cells.

The same-list and other-list counts sum scalar exact comparisons. For example, one contribution for A001 against the first B record is:

=--EXACT("ID:"&'RawA'!B5,"ID:"&'RawB'!B5)

It contributes 0 because 0012 differs from 12. The comparison against RawB B6 contributes 1 because that cell contains 0012. The formula sums contributions over B5:B14. The fixed ID: prefix is added to both operands within the calculation, protecting numeric-looking strings from coercion. It does not change the raw ID or make two different strings equal. Scalar comparisons also keep the supplied calculation engine from treating EXACT as an unsupported array operation.

The classification for row 15 is:

=IF(LEN(C15)=0,"Blank ID",IF(E15>0,"Matched",IF(A15="A","Left-only","Right-only")))

First in source is 1 only for the first occurrence of each nonblank key within its own list. Summing it gives each list's distinct-key count. First overall works across all twenty Results rows, so its sum gives the union rather than adding the two source distinct counts.

Attention checks missing IDs, own-list counts above one and possible whitespace counterparts. A whitespace candidate has no exact counterpart but does have a counterpart after the formula's TRIM operation. TRIM is used to propose review, never to establish membership. This fixture concerns a single trailing ASCII space. The candidate check is not a general detector for every Unicode whitespace or normalization issue.

The workbook is a fixed ten-plus-ten-row example. Changing existing ID cells recalculates its formulas. To use larger lists, extend both raw source ranges, result rows, prefix ranges for first-occurrence counts, exception links and summary ranges, then rerun the boundary checks. Adding a raw row alone does not extend this model. Keep a fresh copy of the source before replacing the teaching inputs.

A complete wrong result

Here is a deliberately authored wrong method: drop blank IDs, trim spaces, lowercase text, turn all-digit strings into numbers, retain only the first row per resulting key, then compare those keys. It looks tidy and returns eight rows:

The complete file is wrong-results.csv. This is a constructed teaching error, not an output attributed to a tested model or person.

Three purported matches are unsupported by the stated rules:

• A001 "0012" is paired with B001 "12" after numeric conversion. The true exact counterpart of A001 is B002. A002 is the exact counterpart of B001.
• A007 "ABC" is paired with B006 "abc" after case folding, contrary to the explicit case rule.
• A008 "Q-7 " is paired with B007 "Q-7" after trimming. That silently answers the one business question that is still unresolved.

Six original rows disappear entirely:

Calling this “six matches and two unmatched devices” would confuse normalized keys, source rows and physical devices. The eight-row output represents only fourteen source rowIDs. A neat count does not repair the lost records.

Repair the method and return every row

Reload the untouched originals, remove the unapproved normalization and first-match deduplication, and classify each source row independently. Retain blank rows and add occurrence counts before summarizing. The complete repaired result is:

An empty count is shown as n.a. for blank-ID rows. A count of 0 means a real nonblank ID was compared and found no exact counterpart. These states have different meanings.

A003 and A004 both remain Matched with one counterpart each. B003 sees two A occurrences. A010 sees two B occurrences, while B009 and B010 each see one A occurrence. These are occurrence counts within membership comparison, not chosen pairings.

Check rows and distinct keys separately

The shared keys are 0012, 12, D-10, 0 and E-2. Each source has three distinct keys absent from the other under exact equality. The union is 8 + 8 − 5 = 11. The row reconciliation is 12 matched + 3 left-only + 3 right-only + 2 blank = 20. Twelve matched source rows do not mean twelve matched pairs or twelve distinct shared devices.

The full request

Attach source-lists.json, which contains both complete lists shown above. The two CSVs are optional copies. Give this request with those inputs rather than asking only to “find the differences”:

Build a reconciliation workbook from the attached source-lists.json. RawA and RawB each contain ten original fictional device-ID records. JSON strings are text identifiers; null is an empty ID cell. raw-a.csv and raw-b.csv are equivalent CSV copies, but JSON defines the intended types.

Return an actual .xlsx with RawA, RawB, Results and Exceptions, plus a complete row-level results CSV. Preserve all source rowIDs, order, original text, leading zeros, duplicates and empty ID cells. Keep RawA and RawB intact. Show ID literals in quotation marks in a separate display field so a trailing space is visible. Do not put the quotation marks into the raw ID cells.

Use case-sensitive exact text equality. "0012" and "12" are different. "ABC" and "abc" are different. "0" is a valid text ID, not a blank. Do not trim, numerically coerce, case-fold, fuzzily merge or silently deduplicate identifiers.

This is source-row membership reconciliation, not one-to-one allocation. Give every original row one Results row. A nonblank ID is Matched if at least one exactly equal ID exists in the other list. Otherwise use Left-only for A or Right-only for B. Keep blank-ID rows as Blank ID and exclude them from matching and distinct-key counts. Report own-list and other-list occurrence counts for every nonblank source row. Flag every row participating in a same-source duplicate, not just the extra occurrence. Do not discard any source record or expand duplicate keys into a many-to-many join.

Show row counts separately from distinct nonblank-key counts. The combined distinct count is the union, and the matched distinct count is the intersection. Explain why matched source rows are not matched pairs.

One business rule is unresolved: A008 is "Q-7 " with one trailing ASCII space, while B007 is "Q-7". Report exact-string membership provisionally, flag both for whitespace review, and ask whether these are the same identity. Do not guess a resolution. A record of a decision must not pretend to apply an unimplemented alias rule.

Use recalculating formulas for the worked example, including source links, occurrence counts, classifications, flags and summary counts. Document the fixed ranges and how adding rows requires extending them. Keep the exception view traceable by source rowID and make flagged rows easy to isolate. Preserve zero, blank and duplicate as different states.

Return the complete deliberately wrong first-match/normalized/deduplicated output and explain every lost source row and false match. Then return the complete repaired result. Verify the actual workbook against the supplied source records, including changes from zero to blank, a duplicate to a new ID and a case change, restoring the originals afterward. State which calculation engine was tested.

Missing from the other list establishes absence from that supplied snapshot only. Do not infer inactivity, stock quantity, ownership or authorization to delete. Do not change a live inventory, send messages or take external action. Ask the whitespace question even after the technical repair succeeds.

You can dictate the comparison brief if that helps you state the rules, then review the typed IDs and exceptions before using it. This example does not measure voice efficiency or assume that spoken identifiers were captured correctly.

Check the downloaded result before using your own data

Confirm that A001 and B002 still contain the text 0012, A006 and B005 contain text 0, and the two blank source cells are empty. Verify both duplicate groups and all twenty source rowIDs. Check the five distinct shared keys separately from the twelve matched source rows.

This workbook's formulas were recalculated in Artifact Tool and compared row by row with a separate JavaScript calculation using exact string equality. Temporary changes tested zero becoming blank, a duplicate becoming a new key and a case change. The original data was restored afterward. The saved workbook package was checked for source text, blank cells and formulas. Native Microsoft Excel execution and a model or human usability trial were not performed.

The technical repair does not settle the whitespace rule. Keep its two rows visible until that question is answered. No result here authorizes deletion from a source system.