Text Tools·4 min read

How to Compare Two Lists and Find the Differences

Two lists that should match rarely do. The newsletter export has 1,204 addresses and the CRM has 1,187, and somebody needs to know which 17 are missing, plus which ones exist in the CRM but never signed up. Comparing two lists is set arithmetic: what is only in the first, what is only in the second, what is in both. The arithmetic is simple. What makes it hard in practice is that the same item is often spelled slightly differently in each list. This guide covers the four results a comparison gives you, how to prepare the lists so matches are real, and how to do the same job in a spreadsheet.

Open the free Compare Two Lists All insights

The four answers a list comparison gives

Difference, intersection, union.

Compare Two Lists shows four results side by side. Only in List A is the difference A minus B: everything in the first list that the second one lacks. Only in List B is the reverse. In both lists is the intersection, the items the two share. All unique items is the union, every item from either list with repeats removed.

Most questions map to exactly one of these. Which signups are missing from the CRM is Only in List A with the signup list as A. Which products were discontinued is Only in List A with the old catalog as A and the new one as B. Who attended both workshops is In both lists. A combined mailing list with no duplicate sends is All unique items.

Why identical items fail to match

Computers compare characters, not meaning.

A comparison only matches items whose characters are the same, so small differences split what a person would call one item into two. The usual suspects are capital letters, spaces at the start or end, and invisible characters picked up from web pages or PDFs.

That is why Ignore case and Ignore spaces at the start and end are on by default. With both ticked, Bob@Example.com with a trailing space matches bob@example.com. Turn Ignore case off only when capitals really change the meaning, as they do for product codes, passwords and file paths on Linux servers.

Different capitals: Apple versus apple.
Padding: a trailing space copied from a spreadsheet cell.
Hidden characters: a non-breaking space or zero-width space from a web page. Clean these first with the AI Watermark Remover or Trim Whitespace.
Formatting: +1 555 0100 versus 5550100. Normalize phone numbers and dates before comparing.

Worked example: two email lists

Five addresses each, seven in total.

The built-in sample has five addresses in each list. List A holds alice, bob, carol, dave and Erin at example.com. List B holds bob, erin, frank, carol and grace. With the default options, Only in List A shows alice and dave, Only in List B shows frank and grace, and In both lists shows bob, carol and Erin. Erin and erin count as one person because case is ignored, and the spelling from List A is the one shown.

All unique items lists seven addresses: the five from List A in their original order, then frank and grace. The labels above the lists also show the item count and the unique count for each side, which quietly tells you whether either list had duplicates of its own.

Lists that are not one item per line

Commas, tabs and other separators.

Lists arrive in many shapes: a comma-separated string from a form, a semicolon list from a European CSV export, a tab-separated row copied from a sheet. Set Items are separated by to match what you pasted, and pick a separate format for the answers in Separate results with. Comparing two comma lists and getting the differences back as a column is a common combination, since a column pastes cleanly into a spreadsheet.

Each result can also be sorted A to Z, which makes long outputs easier to scan, and copied or downloaded as a .txt file on its own.

Comparing two lists in Excel or Google Sheets

Formulas that do the same job.

If the lists already live in a spreadsheet, formulas can answer one question at a time. With list A in column A and list B in column B, =COUNTIF(B:B,A2)=0 returns TRUE for values in A that are missing from B, and filling it down marks every one. Swap the ranges for the reverse. In Excel 365 and Google Sheets, =FILTER(A2:A500,COUNTIF(B2:B500,A2:A500)=0) returns the missing items as a list.

COUNTIF ignores case, so it behaves like Ignore case, but it does not trim spaces, so wrap the values in TRIM when padding is likely. For one-off checks, pasting both columns here gives all four results at once without building helper columns, and nothing in the original sheet changes, which matters when the file is shared with a team.

Mistakes that give the wrong answer

Worth a ten second check.

Before trusting the numbers, rule these out:

Pasting the lists into the wrong boxes, which swaps Only in A and Only in B. The Swap lists button fixes it in one click.
Comparing a list with a header row against one without, which adds a false difference.
Leaving Ignore case on for case-sensitive codes, which merges items that are really different.
Comparing different formats of the same data, such as dates written as 03/04 and 2026-04-03.