Compare two lists without false matches

Start with a reference that identifies the same record

You have two exports and want to know what changed. Sorting by amount seems quick, until several records have the same value. A total can agree while individual records are missing. This comparison makes differences visible without changing either source.

Choose a reference that identifies the same record in both lists: an order number, document number or another shared identifier. Confirm that each row describes the same kind of thing and that both exports cover the intended period. A list of orders cannot be paired directly with a list of order items. If references are only unique within a branch or year, prepare a shared, unambiguous identifier before using this tool.

Try the example: identical amounts, different records

Load the synthetic example above and select Compare lists. List A has seven rows; B has six. The result contains seven reference groups: two matches, two differences, one only in A, one only in B and one repeated reference. All records are synthetic.

References 001 and 002 both have 125.00 USD in A. In B, 001 and 009 have that same amount. Reference 001 matches. Reference 002 remains only in A, while 009 remains only in B. Pairing 002 with 009 because both say 125.00 would invent a relationship.

Reference 003 shows an amount difference. Reference 004 shows a currency difference even though its number is unchanged. Reference 005 repeats in A, so its entire group stays unpaired, including the row in B. Reference 006 demonstrates that an equal negative amount can match.

Prepare three columns with an explicit format

Paste reference;amount;currency, one record per line. The optional header can use those English names or referencia;importe;moneda. Each list accepts up to 200 data rows and 40,000 characters. Blank lines are ignored. The tool accepts this limited semicolon format, without quoted fields; it is not a general CSV importer.

Use a decimal dot and at most two decimal places. Negative values are valid, but grouping separators, scientific notation and more than twelve whole digits are rejected. A format error blocks the whole comparison and identifies its source line. Fix the source deliberately; replacing punctuation everywhere can change a value.

• Keep identifiers such as 001 as text. Reference case matters: AB1 and ab1 remain different.
• Spaces around each field are trimmed; internal reference characters stay unchanged.
• Currency codes must contain three letters and become uppercase. The tool does not verify official currency codes or convert currencies.

Read five outcomes without deleting evidence

The summary counts distinct references, not input rows. Open each exception with its original line numbers. A repeated reference may indicate a duplicated export, a split transaction or the wrong choice of key. Identical-looking rows do not settle that question.

• Match: one row on each side, with equal amount and currency.
• Different: one row on each side, but the amount or currency differs.
• Only in A: the reference has no corresponding row in B.
• Only in B: the reference has no corresponding row in A.
• Repeated reference: either side contains it more than once. Every row remains visible; none is automatically selected or removed.

In Excel, preserve the references and separate repeats

Download the two examples. In Excel, use Data → From Text/CSV, select the semicolon delimiter and open Transform Data. Remove any automatic Changed Type step, then set reference and currency to Text. Microsoft documents importing identifiers as text to preserve leading zeros. Double-clicking a CSV can reinterpret them; changing the display later does not reliably recover lost information.

For amount, choose Change Type → Using Locale, Fixed decimal number and English (United States). This locale matches the decimal dot in these example files; it is not a required setting for every source. Check that 125.00 remains one hundred twenty-five before continuing. Microsoft explains how locale controls text conversion.

Before grouping, trim leading and trailing spaces from reference and currency; uppercase currency while preserving reference case. In a working copy of each query, select reference, then Group By with Count Rows. Filter counts greater than one. Microsoft documents these grouping operations. Keep those references in an exceptions list and exclude them from both working lists before merging. For the example, set aside every 005 row from A and B. Preserve the original queries and the excluded rows for review; no source record needs to be deleted.

Use a full outer merge on the unique references

Select one prepared query and choose Merge Queries. Pick the other query, select the reference column in each and choose Full outer. Expand the other table’s reference, amount and currency. Microsoft describes this join as retaining rows from both tables, including those without a partner. Keep approximate matching disabled.

Retain both reference columns so you can see which side is absent. Compare amounts only after confirming a shared reference and currency. Treat a missing side as missing, not as a zero amount. Add a status column using the four rules above, then keep the repeated-reference exceptions alongside that result.

Verify the example manually before repeating the process: 002 and 009 must remain separate; 004 must show a currency difference. If either condition fails, inspect the selected join columns and the field types before trusting a larger file.

Turn the result into a short review queue

Download the report when you need to continue the review elsewhere. It contains one row per source record, with a stable status code, reference, source list and original line number. Repeated groups are not exported as invented pairs. Formula-like references receive a protective apostrophe; import the reference column as text to preserve its zeros.

Give each open reference an owner and a question: confirm the export period, find the missing document or explain the differing value. Record the answer before changing an operational system. A match here only confirms the three supplied fields; it does not prove payment, document authenticity or correctness of either source.

The web tool processes pasted text locally without sending or saving it. It does not perform partial payments, currency conversion, approximate matching or accounting entries. For larger files or ambiguous identities, agree on a stronger reference model and a reviewed workflow before automating the next step.