Comparing Two Lists: Set Difference, Intersection and the Gotchas

By Wesley · · 7 min read

"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

List AList BOnly in AcaroldaveIn bothalicebobOnly in Berin
List A: alice, bob, carol, dave. List B: bob, erin, alice.
QuestionSet operationTypical use
What is in A but not in B?Difference A − BWho unsubscribed, which SKUs are missing from the store
What is in B but not in A?Difference B − ANew sign-ups, files added since the last export
What is in both?Intersection A ∩ BCustomers on both plans, duplicate records across systems

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.

ProblemExampleFix
Letter caseAda@Example.com vs ada@example.comCompare case-insensitively (right for emails and domains, wrong for case-sensitive IDs)
Leading or trailing spaces"bob " vs "bob"Trim every item
Invisible charactersNon-breaking space from a web page, a BOM at the start of a file, Windows line endingsStrip them; they survive copy and paste from Excel and HTML
Quotes and delimiters"alice" from a JSON array vs alice from a columnParse each side in its own format first
Number formatting007 vs 7, 1,000 vs 1000Decide: are these numbers or codes? Codes compare as text
Duplicatesbob appears three times in ASets ignore repeats. If counts matter, compare counts separately
Sets versus multisets
A set comparison says whether an item appears, not how often. If list A is a cart with two of the same item and list B has one, a set comparison calls them equal. For inventory or reconciliation work, count occurrences on each side and compare the counts.

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

Python
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)
SQL
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.

The Compare Two Lists tool with a line-separated list on the left, a JSON array on the right, and results split into Only in A, Only in B and In Both
The lists from the diagram at the top of this article, one per line on the left and as a JSON array on the right. Numbers match the steps below.
  1. Step 1: Paste List A, or use Upload to load it from a file. The counter under the box shows how many items were recognized, which is a quick check that the list was split the way you expected.
  2. Step 2: Paste List B. It does not have to be in the same format as List A.
  3. Step 3: Set the comparison options. Leave Compare as on List for set comparison, and turn on Ignore case or Ignore whitespace if the two sources format values differently. Text and JSON modes compare line by line instead.
  4. Step 4: Click Compare.
  5. Step 5: Read the three groups: only in A, only in B, and in both, each with its count and its own Copy button. The similarity figure is the share of all distinct items that appear in both lists.
  6. Step 6: Download CSV saves all three groups in one file for a spreadsheet; Copy Results copies them as text.
Compare Two ListsPaste two lists in any mix of formats and get only-in-A, only-in-B and in-both.

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.

Advertisement