Guide
Comparing two lists usually means checking sign-ups against check-ins, dues owed against payments, or a roster against submitted forms. List A is everyone who should be there. List B is everyone who actually showed up, paid, or submitted something. Paste both lists from Excel or Google Sheets, then choose the column to match. You get a list of who is missing, who is extra, and a Present or Absent column that lines up with list A. No VLOOKUP needed.
How to compare two lists
- Select the rows from list A in Excel or Google Sheets, such as your sign-up sheet. Copy and paste them into List A. You can include multiple columns and a header row.
- Paste list B, such as a check-in sheet or a payment report, into List B. The two lists do not need the same columns.
- Check the match columns. The tool uses an ID or email column when both lists have one and it appears less often than names. Otherwise, it uses names. Phone numbers are only used as a second column.
- Review the results. Only in A shows who is missing. Only in B shows who is not on your list. Needs review shows what to check by hand. Every tab lets you copy the original rows back to your spreadsheet.
- To mark list A, open Status column and copy it. In your spreadsheet, click the empty cell next to the first row of list A and paste.
Differences that are ignored before matching
Two lists often look the same but use slightly different formatting. The first six pairs below count as the same. The next five might be the same person, so they go to Needs review. The last two never match on purpose: two extensions on the same main line and two cells that both say N/A. The Compared as column shows the text that was compared: it is lowercase, and a space is kept only between two letters, so Dr. John Smith appears as dr.john smith.
| Difference | List A | List B | Compared as (A) | Result |
|---|---|---|---|---|
| Two spaces between first and last name | John Smith | John Smith | john smith | Present |
| Space inside an ID | E 001 | E001 | e001 | Present |
| Upper and lower case | JSmith@Example.com | jsmith@example.com | jsmith@example.com | Present |
| Invisible zero-width space at the end | John Smith | John Smith | john smith | Present |
| Phone with parentheses | (415) 555-0123 | 415-555-0123 | 4155550123 | Present |
| Phone with +1 and dots | +1 415 555 0123 | 415.555.0123 | 4155550123 | Present |
| Last name first | Smith, John | John Smith | smith,john | Needs review |
| Suffix Jr. | John Smith Jr. | John Smith | john smith jr. | Needs review |
| Middle initial | John A. Smith | John Smith | john a.smith | Needs review |
| Title before the name | Dr. John Smith | John Smith | dr.john smith | Needs review |
| Note in brackets | John Smith (Sales) | John Smith | john smith(sales) | Needs review |
| Same main line, different extension | 415-555-0100 x101 | 415-555-0100 x102 | 4155550100#101 | Absent |
| Both lists say N/A | N/A | N/A | (not compared) | Blank match value |
You can turn off each rule under Ignore these differences. Phone rules apply only when Match type is Phone. (415) 555-0123, 415-555-0123, 415.555.0123, and +1 415 555 0123 are the same number. Extensions are kept, so x101 and x102 are different people.
Cells that say N/A, TBD, Unknown, None, or only a dash are marked Blank match value, as are phone cells with no digits. They never match a blank in the other list, and they are kept when you merge.
Find the differences between two lists
The results are organized into tabs. Only in A shows rows from list A with no match in list B. Only in B shows rows from list B with no match in list A. In both shows rows found in both lists. Needs review shows rows that might match but need someone to decide.
Each tab copies the full original rows, with the header row first when your list has one. Paste them into a new sheet to create a clean table.
People with the same name
If list A has two people named John Smith and list B has one, you know that one is there but not which one. A comparison that only checks whether the name appears in list B marks both as present, and you miss a person.
This tool matches by count. A name must appear in list B as many times as it appears in list A. When only some match, all of them go to Needs review with a note such as Same name: 2 rows in A, only 1 in B. To tell them apart, add a second match column, such as an employee ID or phone.
If list B has more copies than list A (for example, someone checked in twice), the person in list A counts as present. The extra row does not appear in Only in B. It is listed under Duplicates within a list.
Near matches go to Needs review
These pairs are probably the same person, but not always. A father and son can have the same name except for Jr. They go to Needs review and are not counted as present. The Status column also says Needs review.
After you check them, fix the names in your spreadsheet and paste again.
- Smith, John and John Smith
- John Smith Jr. and John Smith
- Dr. John Smith and John Smith
- John A. Smith and John Smith
- John Smith (Sales) and John Smith
Mark each row as Present or Absent
The Status column tab has one value for every row in list A. The header row gets Match result. Blank rows stay blank. Rows with no match value get Blank match value. The rest get Present, Absent, or Needs review.
Paste it next to list A, then use your spreadsheet filter to see only absent rows or rows that need review. You can switch Present and Absent to Paid and Unpaid, Submitted and Missing, or Yes and No.
Merge two lists and remove duplicates
Merged list combines list A and list B and removes duplicate rows using the match column. The first copy of each row is kept, with list A first. When both lists have column names, list B columns with matching names are placed under the list A columns, and new columns are added at the end.
Copy the result into your spreadsheet, or download a CSV file. The file is UTF-8 with a byte order mark. Rows that only match after removing a suffix or note are not merged. Check them yourself.
Compare two lists in Excel with a formula
If you prefer to work in your spreadsheet, you can use COUNTIF to mark each row. Suppose the names in list A are in column A starting in row 2, and list B is in column A of a sheet named Checkins:
=IF(COUNTIF(Checkins!A:A,A2)>0,"Present","Absent")
To handle people with the same name, compare the number of times the name appears in list B with the number of times it has appeared in list A so far:
=IF(COUNTIF(Checkins!A:A,A2)>=COUNTIF($A$2:A2,A2),"Present","Absent")
With one John Smith in list B, the second John Smith in list A shows Absent. The formula still cannot tell which John Smith arrived. It only uses the order.
COUNTIF does not clean up text first, so extra spaces, invisible characters, and different phone formats prevent a match. Those differences appear in the table above.
Frequently asked questions
How do I find what is missing from one list?
Paste both lists and open the Only in A tab. It shows every row in list A with no match in list B. Only in B shows the opposite.
Can I compare lists with several columns?
Yes. Paste entire rows and choose the column to match. Add a second match column if names repeat. The other columns are kept and copied with each row.
Is Smith, John matched with John Smith?
Not automatically. It appears under Needs review, along with names that differ only by Jr., a title, a middle initial, or a note in brackets.
How many rows can I compare?
Up to 10,000 rows in each list. Each tab shows the first 200 rows on screen. Copying and downloading include all of them.
Does it work with Google Sheets?
Yes. Select the cells in Google Sheets, copy them, and paste them here. You can paste the results back into Google Sheets the same way.
Is my list uploaded?
No. Your lists are compared in your browser. They are not sent to a server, added to the URL, or saved. Analytics records only which buttons you used and how many rows there were, never the contents. The CSV file is also made in your browser.
