Comparing Two Lists: Set Difference, Intersection and the Gotchas

"Compare these two lists" sounds like one task. It is really three questions, and almost every wrong answer comes from skipping a step before the comparison, not from the comparison itself.

Three questions, three sets

A single similarity number is often useful as a summary. The usual one is the Jaccard index: shared items divided by all distinct items. In the example above that is 2 ÷ 5 = 40%. It reaches 100% only when both lists contain exactly the same items, regardless of order.

Normalize before you compare

Two entries that a person would call the same are often different strings. Decide which of these matter for your data before comparing, otherwise the "only in A" column fills up with false positives.

In Excel and Google Sheets

With list A in column A and list B in column B, flag the items of A that are missing from B:

=COUNTIF($B:$B, A2) = 0

Fill that down and filter on TRUE. In Excel 365 and Google Sheets, a single formula returns the whole difference as a list:

Only in A:   =FILTER(A2:A100, COUNTIF(B2:B100, A2:A100) = 0)
In both:     =FILTER(A2:A100, COUNTIF(B2:B100, A2:A100) > 0)

COUNTIF ignores case, which is usually what you want. It does not ignore trailing spaces, so wrap the source columns in TRIM if the data was pasted from elsewhere.

On the command line

comm compares two sorted files and prints three columns: lines only in the first, only in the second, and in both. The flags suppress columns.

comm -23 <(sort -u a.txt) <(sort -u b.txt)   # only in A
comm -13 <(sort -u a.txt) <(sort -u b.txt)   # only in B
comm -12 <(sort -u a.txt) <(sort -u b.txt)   # in both

Unsorted input is the classic mistake: comm does not complain, it just gives wrong results. Both files must also be sorted under the same locale, and files from Windows need their carriage returns removed first (tr -d '\r').

In Python and SQL

a = {line.strip().lower() for line in open("a.txt") if line.strip()}
b = {line.strip().lower() for line in open("b.txt") if line.strip()}

only_a, only_b, both = a - b, b - a, a & b
similarity = len(both) / len(a | b)

SELECT email FROM list_a
EXCEPT
SELECT email FROM list_b;      -- only in A

SELECT email FROM list_a
INTERSECT
SELECT email FROM list_b;      -- in both

EXCEPT and INTERSECT remove duplicates, like sets. They are available in PostgreSQL, SQL Server and SQLite, in MySQL from 8.0.31, and Oracle uses MINUS in place of EXCEPT. On older MySQL versions, use LEFT JOIN … WHERE b.email IS NULL.

Picking the right approach

• Two columns already in a spreadsheet: a COUNTIF column is the least effort.
• Large files or a repeated job: comm or a few lines of Python; both handle millions of lines.
• Data already in a database: EXCEPT / INTERSECT, with no export step.
• Two lists in different formats, once: a browser tool is fastest, because it does the parsing and normalization for you.

The browser option

The Compare Two Lists tool accepts one item per line, comma-separated values or a JSON array on either side, trims items and strips surrounding quotes, and shows the three groups with counts and a similarity score. Ignore-case and ignore-whitespace are switches, and duplicate counts can be shown per list.

Comparing structured documents instead of flat lists? Read how JSON diff works. Need the result as code? See turning a list into an array literal.