Compare Two Lists

How to compare two lists

Paste one list into List A and the other into List B, one item per line, or press Open file to load a .txt or .csv file. Files are read by your browser and never uploaded. Results update as you type. Pick a result above the output box, then copy it or download it as a .txt file.

What each result means

Picture two overlapping circles, A on the left and B on the right. Only in A is the part of A outside the overlap: lines A has and B does not (A minus B). Only in B is the same on the other side. In both is the overlap (the intersection). In A or B is the whole shape (the union), each line listed once, so its count equals the other three added together.

Here are two keyword lists:

And the results, with the default options and with Ignore case ticked:

How the options change matching

• Ignore case treats Keyword research and keyword research as the same line. Leave it off for case-sensitive values such as video IDs or tokens.
• Ignore extra spaces matches lines that differ only in spaces at the ends or in runs of spaces and tabs inside. The output keeps the spacing of the first copy.
• Trim lines removes whitespace at the start and end of every line before comparing, and the output shows the trimmed lines.
• Ignore empty lines (on by default) skips blank lines and reports how many were skipped. Turn it off to treat a blank line as an item.
• Original order keeps A's order, followed by B's order for lines only in B. A to Z sorts without regard to case and compares numbers by value, so item 2 comes before item 10.

Duplicates, spelling and limits

Results never repeat a line. The summary reports repeats inside each list: a line that appears three times in A counts as two duplicates. To clean one list on its own, use Remove Duplicate Lines. When lines match through Ignore case or Ignore extra spaces, the result shows the first spelling found, looking in A first, so In both uses A's spelling.

Each line is compared as a whole, so a CSV row is one item. To compare a single column, pull it out with the CSV Column Extractor. Windows, Mac and Unix line endings all work. In tests, two lists of 100,000 lines each compare in well under a second. Above about 2 MB of text, results stop updating as you type; press Compare instead.

Common uses

• Email lists: last month's subscriber export in A, this month's in B. Only in A shows who left, Only in B who joined. Tick Ignore case.
• Keyword gap analysis: your ranking keywords in A, a competitor's in B. Only in B is the gap to target.
• URLs before and after a migration: the old site's crawl in A, the new site's in B. Only in A lists URLs that disappeared and need a redirect.
• Inventory SKUs: warehouse stock in A, the online store's catalogue in B, to find products that are not listed yet.

If one side is spread over several exports, combine them first with Merge Text Files.

Compare two lists in Excel or Google Sheets

Put list A in column A and list B in column B, starting at row 2. In C2, enter the formula below and fill it down. It labels each row of A:

=IF(COUNTIF($B$2:$B$1000, A2)>0, "In both", "Only in A")

=ISNUMBER(MATCH(A2, $B$2:$B$1000, 0)) does the same job and returns TRUE or FALSE. Both functions ignore case and treat * and ? as wildcards, so lines containing those characters can match more than they should. For a case-sensitive check, use =SUMPRODUCT(--EXACT(A2, $B$2:$B$1000))>0. In Excel 365 and in Google Sheets, one formula can list everything only in A (adjust the ranges to your data):

Excel 365:     =FILTER(A2:A500, COUNTIF(B2:B400, A2:A500)=0)
Google Sheets: =FILTER(A2:A, A2:A<>"", COUNTIF(B2:B, A2:A)=0)

Compare two lists on the command line

comm compares two sorted files and prints three columns: lines only in the first, lines only in the second, and lines in both. Its options hide columns. It needs sorted input, so sort both files first:

sort -u a.txt > a.sorted
sort -u b.txt > b.sorted
comm -23 a.sorted b.sorted   # only in A
comm -13 a.sorted b.sorted   # only in B
comm -12 a.sorted b.sorted   # in both
sort -u a.txt b.txt          # in A or B

If comm warns that a file is not in sorted order, run both commands with LC_ALL=C. To keep the original order, use grep -Fxv -f b.txt a.txt, which prints the lines of a.txt that are not in b.txt: -F reads patterns as plain text, -x matches whole lines, -v inverts the match and -f reads the patterns from b.txt. Drop -v for the lines in both; add -i to ignore case. Strip Windows carriage returns first with tr -d '\r'.