Compare Two Lists to Find Missing Items: Excel and Online Tools

Choose by the job you need to finish

For a one-off pair of single-column lists, a list comparison tool gives you missing and shared members quickly. If the same check already lives in Excel and repeats each week, keep the data and matching rules together in a worksheet or a maintained query. Choose a keyed table-join workflow when you must reconcile names, status, amounts or quantities by ID. Choose a text diff for prose, code or line-order changes.

WorkToolMap and comparetwolists.net are both maintained by Jack. This is a disclosed test of our own product, not an independent review or a ranking of online tools. We tested one online tool and reviewed one Excel formula route against the same base input.

Scroll the table horizontally to see all 3 columns.

Understand the three groups before acting on a missing item

Download base-a.txt and base-b.txt below. The base fixture includes 00123, 123, two spellings of SKU-A with outer spaces, Apple/apple/APPLE, blank lines, 广州 and the literal value A,B. It contains no personal data.

With Trim outer whitespace on and Ignore case off, Only in A is 00123, Apple, apple. Only in B is APPLE, B-ONLY. In both is 123, ␠SKU-A␠, 广州, A,B. Here ␠ marks one literal space for readability; the downloadable output retains the original spaces.

Only in A means an A member has no matching member in B under these settings. It does not establish why it is missing, whether a person is the same person, or whether an order was cancelled. In both uses the first original A spelling and A order.

Scroll the table horizontally to see all 4 columns.

A duplicate is different from a shared member

In the default run A had 8 nonblank input rows, 7 distinct values and 1 duplicate extra row. B had 6 nonblank rows and 6 distinct values. SKU-A appears twice in A, but it is one membership group and is also shared with B. Expanding its result showed A lines 3 and 4 and B line 2. A duplicate group can therefore also be in the intersection.

Turning Ignore case on combines Apple and apple in A and matches APPLE in B. It changes the groups; it does not repair a defect. Turning trim off makes the spaced SKU-A and A,B values different from their unspaced versions. Blank and ASCII-space-only rows were ignored in all three runs. These examples do not establish general Unicode normalization behavior.

Run the online path with the downloadable inputs

Open Compare Two Lists, paste base-a.txt into A and base-b.txt into B, leave Trim outer whitespace on and Ignore case off, then compare. Check the three groups above and expand 00123 to inspect its source. It is different from 123 because these are text identifiers.

For the CSV route, import base-a.csv and base-b.csv. Keep comma as the delimiter, enable the header option and select ID, the second column. The other column deliberately contains Chinese text and quoted commas. A,B is also a quoted ID. Selecting the first column would compare notes rather than IDs; change the selection before interpreting the answer.

For XLSX, import base.xlsx on both sides. Choose AInputs for A and BInputs for B, enable the header option and select ID (column B). The Readme worksheet is deliberately not the input. Our run reproduced the default three groups and showed CSV record 2, column 2 and XLSX AInputs, cell B2 for 00123.

A separate pasted-text check kept 12345678901234567890 distinct from 12345678901234567891 and preserved the leading zeros in 00012345678901234567890. The XLSX sample contains text cells; this test does not prove recovery of digits already lost in numeric cells, or correct handling of formulas, hidden rows or formatted numeric identifiers.

Changing either the pasted input or Ignore case disabled copying and hid the old save link until a new comparison completed. Always rerun after changing an input or a rule.

• Compare Two Lists — task notes and website — On 9 October 2026, our synthetic default-rule sample returned 3, 2, 4 distinct values in Only in A, Only in B and In both. CSV and text-cell XLSX imports reproduced those groups after selecting ID and excluding headers. Each 125-item export group was copied and downloaded in full, then reread.

An Excel route for a repeated worksheet check — not hands-on tested

This route is a documented method, not a measured competitor result. We could not operate a verified Excel workbook in this session. The formulas below are derived from Microsoft’s documented functions and were not executed in Excel for this guide.

Use the same base inputs: format A2:A10 and B2:B8 as Text before placing the values, retain blank rows, and keep the originals. In C2 enter =TRIM(A2) and fill through C10. In D2 enter =TRIM(B2) and fill through D8. TRIM also collapses repeated internal ASCII spaces, unlike the online tool’s outer-only trim; these base values contain no internal space runs. It is not an interchangeable cleanup rule for arbitrary data.

For the A-side label, enter =IF(C2="","",IF(SUMPRODUCT(--EXACT(C2,$D$2:$D$8))=0,"Only in A","In both")) and fill down. For B, swap the ranges: =IF(D2="","",IF(SUMPRODUCT(--EXACT(D2,$C$2:$C$10))=0,"Only in B","In both")). EXACT makes this path case-sensitive. Function names and separators may differ in localized Excel.

These labels retain repeated rows. To select only the first member of each A group, add =IF(C2="","",SUMPRODUCT(--EXACT(C2,$C$2:C2))=1) and fill down; filter TRUE together with the wanted label. Use the corresponding expanding D range for B. Export original A values for A-only/shared and original B values for B-only. Check the frozen F01 expected file before relying on the worksheet.

For ignore-case matching, use LOWER around both helper-column formulas and compare with F02. Keep originals separate. This option is still untested here. For repeated joins or quantity reconciliation, design a keyed table workflow instead of treating distinct membership counts as quantities.

Verify the whole download, not just the preview

The larger input puts ITEM0001 through ITEM0250 in A and ITEM0126 through ITEM0375 in B, one value per line. The frozen answer is 125 in each group: A-only ITEM0001–ITEM0125, B-only ITEM0251–ITEM0375, shared ITEM0126–ITEM0250.

In Chrome 152 on the production version identified in observed.json, Copy returned all 125 values for every group. We downloaded all three groups as TXT and CSV and one XLSX with three sheets, then reread every record using an independent CSV/XML parser. Every group matched the complete expected values and order. The desktop preview displayed 100 rows on its first page; copied and downloaded output went beyond it.

The base XLSX export was also reread: 00123 was still a text cell. TXT has one value per line; CSV and XLSX include a Value header. Preserve text types when opening exports in another program: this test does not guarantee that a spreadsheet’s automatic import will preserve every identifier.

A practical check is to compare the row count, first and last IDs, and full values against expected.json. If a value is missing, inspect the selected sheet/column, header option, matching settings and original source first. Resolve copy or download failures before using the result.

Need to edit a CSV before comparing its IDs?

Preserve the saved CSV before comparing one ID column. A membership result cannot reveal unintended edits to other fields. Compare Two Lists is a follow-up list check, not a general CSV editor.

Downloads and reproducible run

All files below are synthetic. expected.json was frozen before the product runs using Python’s standard library and the reference specification, without importing product code. observed.json records the actual production version, settings, outputs, checks and limits. README.md describes the steps and the document-only Excel route.

Only one online product was tested, so there is no online-tool ranking. This evidence does not cover multi-column joins, quantity reconciliation, all Unicode rules, formulas or data recovery. If you need those, choose a workflow designed for that job.

• Base List A — TXT ↓Synthetic data; no personal records.
• Base List B — TXT ↓Synthetic data; no personal records.
• Base List A — CSV ↓Synthetic data; no personal records.
• Base List B — CSV ↓Synthetic data; no personal records.
• Base input — XLSX with named worksheets ↓Synthetic data; no personal records.
• 250-item List A — TXT ↓Synthetic data; no personal records.
• 250-item List B — TXT ↓Synthetic data; no personal records.
• Frozen independent expected results ↓Synthetic data; no personal records.
• Actual results and test scope ↓Synthetic data; no personal records.
• Actual 125-item groups — XLSX ↓Synthetic data; no personal records.
• Actual base result — XLSX ↓Synthetic data; no personal records.
• Run instructions and file map ↓Synthetic data; no personal records.
• Download sizes and SHA-256 checksums ↓Synthetic data; no personal records.
• Actual Only in A — TXT ↓Synthetic data; no personal records.
• Actual Only in A — CSV ↓Synthetic data; no personal records.
• Actual Only in B — TXT ↓Synthetic data; no personal records.
• Actual Only in B — CSV ↓Synthetic data; no personal records.
• Actual In both — TXT ↓Synthetic data; no personal records.
• Actual In both — CSV ↓Synthetic data; no personal records.