Compare & combine files · 8 min read

Compare Two Lists in Excel: Find Missing IDs and Duplicate Counts

Two lists contain the same kind of ID but use a different order. You want to know which IDs disappeared, which arrived, and whether repeated IDs changed in number. Comparing cells on the same row cannot answer all three questions.

JHSOFT editorialUpdated September 10, 2026

WORKED EXAMPLESynthetic data

List A, enter in A1:A8
id
A101
A102
A102
A103
 A104
CaseA
[empty]

List B, enter in D1:D7
id
A102
A103
A104
casea
A105
[empty]

The List A value before A104 contains one leading space.

With case-insensitive, space-preserving matching: only in A are A101 and the value with a leading space before A104; only in B are A104 and A105. A102 appears in both lists but occurs twice in A and once in B. CaseA matches casea under this rule. Blank entries are marked Skip blank.

Download list-a.csv ↓Download list-b.csv ↓Download list-a-expected.csv ↓Download list-b-expected.csv ↓Download counts-expected.csv ↓Download only-in-a.csv ↓Download only-in-b.csv ↓Download matches-from-a.csv ↓Download comparison-formulas.txt ↓

Choose what counts as the same item

This method works in desktop Excel 2016 and later. It uses bounded ranges and helper columns, so newer dynamic array functions are optional. The formulas use commas as separators. They assume the comparison fields contain text identifiers rather than a mixture of numeric quantities and formatted IDs.

The starting rule ignores letter case, preserves spaces, treats wildcard characters literally, and excludes blank keys from membership decisions. It compares the ID field only. It does not establish that names, amounts, or other fields associated with a shared ID stayed the same. Write down a different rule before changing the formulas.

Question	Method	What it establishes
Does this ID appear anywhere?	Membership in the other list	Presence, regardless of row order
Are repeated records balanced?	Counts in each list	Occurrence difference
Is letter case meaningful?	EXACT-based membership	Case-sensitive text match
Did associated amounts change?	Join by a reviewed key	Separate field comparison
Highlight missing IDs	Conditional formatting from status columns	Visible differences without deleting records
Copy a missing-items list	FILTER in Excel 2021/2024 or Microsoft 365	Separate output that preserves repeated rows
Lists on different worksheets	Sheet-qualified comparison ranges	Same matching policy across two tabs

Prepare two lists without aligning their rows

Enter List A in A1:A8 and List B in D1:D7 on one worksheet. Add status_A in B1 and status_B in E1. Leave the marked blank cells empty. The leading space before A104 in List A is intentional: preserve it so you can see how the comparison rule affects the answer.

If using the downloadable samples, put their ID columns in those positions. You do not need to sort the lists or make them the same length. Keep a source copy before applying any cleanup. Make identifiers Text before importing when leading zeros or long digit strings matter; formatting after conversion cannot restore lost characters.

Check that List A has seven data positions, including its final blank.
Check that List B has six data positions, including its final blank.
Keep rows 2–8 as the A range and rows 2–7 as the B range in the following formulas.

Find values that appear only in List A

Enter the formula in B2 and fill through B8. SUMPRODUCT counts how many cells in the other list equal the current ID. A zero count means the value is absent. The outer IF stops an empty ID from matching an empty cell in the other list.

For this sample, filter column B to Only in A. You should see A101 and the leading-space version of A104. Both copies of A102 say In both, because membership asks whether at least one copy exists elsewhere. The formula does not pair up repeated records one by one.

Enter in B2, then fill through B8

=IF(A2="","Skip blank",IF(SUMPRODUCT(--($D$2:$D$7=A2))=0,"Only in A","In both"))

A101 is absent, both A102 rows have a match, and CaseA matches casea under the selected rule. — Illustrated example. Synthetic comparison. Presence does not prove equal occurrence counts.

Check the other direction

Enter the reverse formula in E2 and fill through E7. Filtering to Only in B returns A104 without the leading space and A105. Looking only at the A-side results would miss these additions, because no A row asks about A105.

Keep the directional labels when sharing the report. Rename the lists meaningfully, such as Previous and Current: Only in Previous means missing from the current list, while Only in Current means newly present. This describes the compared snapshots; it does not by itself prove that someone intentionally deleted or created a record.

Enter in E2, then fill through E7

=IF(D2="","Skip blank",IF(SUMPRODUCT(--($A$2:$A$8=D2))=0,"Only in B","In both"))

Compare occurrence counts for repeated IDs

Create a small count report with id in H1, count_A in I1, count_B in J1, and A_minus_B in K1. Enter A102 in H2 and use the formulas below. The difference is 1: one more occurrence appears in A, despite a match being found on every A102 row.

For the complete sample audit, enter A101, A102, A103, the leading-space A104, CaseA, A104 without a space, and A105 in H2:H8, one per row. Fill I2:K2 through row 8. Treat CaseA and casea as one candidate under the default matching rule. The counts-expected.csv download contains the resulting seven-row report. For your own files, build a reviewed candidate list from both sources using the same equality and cleanup policy; do not remove repeated source rows before counting.

The example reconciles six nonblank A records and five nonblank B records. Counts sum to those totals. A count difference identifies an imbalance, but without a unique transaction key it cannot identify which repeated occurrence should be paired or removed.

Occurrence audit for H2 = A102

I2: =SUMPRODUCT(--($A$2:$A$8=H2))
J2: =SUMPRODUCT(--($D$2:$D$7=H2))
K2: =I2-J2

Expected: count_A = 2, count_B = 1, A_minus_B = 1

Two A102 occurrences in List A and one in List B produce a count difference of one. — Illustrated example. Synthetic occurrence audit. Shared IDs can still have unequal counts.

Change case handling without silently changing other rules

For a case-sensitive A-side result, replace B2 with the EXACT version and fill down. CaseA now says Only in A because List B contains casea. For the reverse check, use EXACT(D2,$A$2:$A$8) with the same blank guard and Only in B label. Apply EXACT to count formulas too if you want a consistent case-sensitive count report.

Do not trim automatically just to make the missing list shorter. The leading space may be an entry error, but cleanup is a separate decision. If approved, create cleaned helper columns in both lists and compare those while retaining originals. Ordinary TRIM does not remove every invisible Unicode character.

Case-sensitive alternative for B2

=IF(A2="","Skip blank",IF(SUMPRODUCT(--EXACT(A2,$D$2:$D$7))=0,"Only in A","In both"))

Highlight differences with conditional formatting

After filling the status formulas, select A2:A8. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =$B2="Only in A", choose a fill color, and confirm. For D2:D7, add a separate rule using =$E2="Only in B". The column is fixed; the row must remain relative.

The sample highlights A2 and A6 on the left, and D4 and D6 on the right. For matches, use the same selected ranges with =$B2="In both" or =$E2="In both". Keep the written statuses so color is not the only way to understand the result.

Highlight Cells Rules > Duplicate Values across a combined selection can also flag repeats inside one list. That is not proof of a match in the other list. Here the color follows the already-reviewed cross-list status, including its blank and case rules.

Two independent formatting rules; do not paste into worksheet cells

Applies to: =$A$2:$A$8
Formula: =$B2="Only in A"

Applies to: =$D$2:$D$7
Formula: =$E2="Only in B"

Extract missing values and matches into separate lists

In Excel 2021, Excel 2024 or Microsoft 365, use FILTER with the existing status columns. Put the three formulas below in M2, O2 and Q2, outside an Excel Table, with empty space underneath. They return A-only values, B-only values and A-side matches. The last argument displays a message when nothing qualifies.

Expected outputs: M2:M3 contains A101 and the leading-space A104; O2:O3 contains A104 and A105; Q2:Q5 contains A102, A102, A103 and CaseA. Compare them with only-in-a.csv, only-in-b.csv and matches-from-a.csv. Matches preserve both A102 rows and A-side spelling. They are not a unique-ID list.

If FILTER is unavailable, use Data > Filter on A1:B8 or D1:E7 and select the required status. For a frozen export, copy the reviewed result to a separate sheet as values. A #SPILL! error means the output needs attention: check for occupied or merged cells underneath and keep the output outside a Table.

Extract from the same-sheet sample; Microsoft 365 / Excel 2021 / 2024

M2: =FILTER(A2:A8,B2:B8="Only in A","No missing IDs")
O2: =FILTER(D2:D7,E2:E7="Only in B","No missing IDs")
Q2: =FILTER(A2:A8,B2:B8="In both","No matches")

Compare two lists on different sheets

For this alternative layout, create sheets named Previous and Current in the same workbook. Put list-a.csv IDs in Previous!A1:A8 and list-b.csv IDs in Current!A1:A7. Add status in B1 of each sheet. Use the formulas below in the specified sheet, then fill down to its last input row.

Previous returns Only in Previous for A101 and the leading-space A104. Current returns Only in Current for A104 and A105. The comparisons still ignore case and preserve spaces. This is membership checking, not comparison of other columns attached to the ID.

If a sheet name contains spaces, enclose it in single quotes, for example 'Current export'!$A$2:$A$7. The extraction formulas above belong to the earlier same-sheet layout; on separate sheets, point each FILTER at that sheet's A and B columns and use its new status text.

Alternative two-sheet layout in the same workbook

Previous!B2, fill to B8:
=IF(A2="","Skip blank",IF(SUMPRODUCT(--(Current!$A$2:$A$7=A2))=0,"Only in Previous","In both"))

Current!B2, fill to B7:
=IF(A2="","Skip blank",IF(SUMPRODUCT(--(Previous!$A$2:$A$8=A2))=0,"Only in Current","In both"))

Validate the result before exporting

Check the known cases: A101 is missing from B, A105 is new to B, A102 has an occurrence imbalance, and the A104 space creates a difference. Empty strings from formulas are treated as blank by this guard; cells containing spaces are not. Correct source error values before calculating the report.

This is an Excel text comparison, not a byte-level file comparison or a check of formatting. If identifier precision was already lost during import, matching formulas cannot reconstruct it. Keep source types intact, use bounded ranges for performance, and expand both the comparison and count formulas when lists grow. Export the reviewed statuses and counts together so their meaning remains visible.

Compare B2:B8 and E2:E7 with list-a-expected.csv and list-b-expected.csv. These downloads contain the expected case-insensitive results, not spreadsheet formulas. Import their ID columns as Text and preserve the intentional leading space. The counts-expected.csv file lets you check the complete occurrence report separately. If you switch to case-sensitive matching, recompute your expected results: the default comparison files will no longer match that policy.

Common questions

Why not use A2=D2 for the comparison?

That checks the two cells on the same row. It is useful only when rows are intentionally aligned. These lists are unordered, so an ID can be present on a different row and should still count as present.

Can I use COUNTIF instead of SUMPRODUCT?

Yes, for a suitable matching rule, but COUNTIF interprets asterisk and question-mark criteria as wildcards unless escaped. The equality tests here avoid that wildcard behavior. They still ignore case unless you use the EXACT alternative.

Does an empty missing-items report mean the lists are identical?

No. Check both directions, occurrence counts, and any other fields that matter. Two lists can contain the same distinct IDs while repeating them different numbers of times or assigning different amounts to each ID.

How do I compare two Excel columns for matches without sorting?

Use the whole other-column range in the membership formula, not the cell on the same row. Filter the resulting status to In both. The FILTER example returns matching A-side records in their original order and preserves repeated rows.

Sources & method

Examples and diagrams use synthetic data. Application instructions follow the linked documentation; available menus and options can vary. Our sample checks do not establish behavior in every Excel or Google Sheets version. Read our AI-assisted editorial method.

Browse all field guides →