Compare two lists

Paste list A and list B. You get the values that are in both, only in A and only in B, even when a spreadsheet has added spaces, changed the case or dropped leading zeros from your IDs.

List A

Nothing pasted yet

List B

Nothing pasted yet

0Only in A

0In both

0Only in B

0All unique

How to match

Each list is separated by

Count values as the same even if they differ in

Hidden characters are non-breaking spaces, zero-width characters and tabs inside a value. Leading zeros make 00123 match 123.

Phone numbers

Turns 09X XXX XXXX and +260 9X XXX XXXX into the same number before comparing. Choose the country the local numbers belong to.

Numbers Excel has shortened

Excel replaces a long ID with a short form such as 3.5E+14 and the rest of the digits are gone. Two different IDs can end up looking the same, so these are listed and not matched.

Result —

Show as

How to compare two lists

Paste list A on the left and list B on the right. One value per line is the default. Switch to commas or tabs under How to match, or drop a text or CSV file on either box.
Read the counters. They show how many values are only in A, in both, and only in B.
Open a tab to see the values. Pick one per line, comma separated or SQL IN, then copy it or save it as a CSV with a column for each list.

Matching ignores the things that most often break it: spaces at the ends, doubled spaces, hidden characters and upper or lower case. Leading zeros and phone number format are options. The note under the counters says how many values each option changed, so you can see what it did.

If you need the result in a different shape, such as quoted values or another separator, send it through the list converter.

Compare on one column

Copy two or more columns from Excel and paste them. The tool sees the tabs and offers Compare on with a column to choose. Pick the column that holds the ID. The results keep the whole row, so you can paste them straight back into a sheet.

The Excel and Google Sheets way

With list A in column A and list B in column B, any of these flags the values in A that are not in B. Put one in C2 and fill it down. Swap the columns to get the values only in B.

COUNTIF

Returns TRUE when the value is not in B.

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

MATCH

Works in every version of Excel.

=ISNA(MATCH(A2,$B:$B,0))

XLOOKUP

Shows the text you choose when the value is missing. Microsoft says XLOOKUP is not available in Excel 2016 and Excel 2019, so use MATCH there.

=XLOOKUP(A2,$B$2:$B$5000,$B$2:$B$5000,"only in A")

Google Sheets

One formula lists every value in A that is missing from B:

=FILTER(A2:A,ISNA(MATCH(A2:A,B2:B,0)),A2:A<>"")

Why the match fails in Excel

Two IDs that look the same on screen can still differ. These are the usual causes.

Spaces at the end. AB12 and AB12 are different values. TRIM fixes ordinary spaces.
Non-breaking spaces. Systems and web pages often add them. Microsoft's TRIM page says the function "does not remove this nonbreaking space character". Use SUBSTITUTE(A2,CHAR(160)," ") first.
Number against text. A 12345 stored as a number does not match 12345 stored as text in MATCH or XLOOKUP. Formatting one column differently is enough to cause it.
Lost leading zeros. Excel treats a column of digits as numbers and drops the zeros in front, so 00123 becomes 123.
Long numbers cut at 15 digits. Microsoft's page Keeping leading zeros and large numbers says Excel "has a maximum precision of 15 significant digits", so any digit after the 15th is rounded down to zero. A 15-digit IMEI survives. A 19-digit SIM number (ICCID) does not, and Excel often shows it as 8.92601E+18. Format the column as Text before you paste.

The last two are the ones to watch in telecom data. Local and international numbers also fail to match each other. The phone number formatter converts a whole column to one format, or switch on the phone option above and compare them as they are.

Opening the CSV you save from here with a double click in Excel can shorten long IDs again. Use Data, then From Text/CSV, and set the ID column to Text.

Questions

How do I find what is in list A but not in list B?

Paste list A in the left box and list B in the right box, then open the Only in A tab. In Excel, put =COUNTIF($B:$B,A2)=0 next to the first value in A and fill it down. Every TRUE is a value that is missing from B.

Why do two IDs that look identical not match?

Usually one of them has a space at the end, a non-breaking space, a different case, or a leading zero that the other lost. Excel's TRIM does not remove a non-breaking space. This tool ignores spaces, hidden characters and case by default, and can ignore leading zeros too.

Can I compare phone numbers written in different formats?

Yes. Switch on the phone number option and choose the country. A local number such as 09X XXX XXXX then matches +260 9X XXX XXXX, because both are turned into the same international form before they are compared.

How many lines can I compare?

We have timed 100,000 lines in each list at well under a second on an ordinary laptop. You can paste them, or drop a text or CSV file of up to 5 MB on either box.

Are my lists uploaded anywhere?

No. The comparison runs in your browser and nothing you paste is sent to a server, saved or put in the address. Only your option choices are remembered on your device. The privacy policy has the detail.

If your team reconciles the same two reports every week, Mbule.Tech can build a tool that does it with your own columns and rules.