π§Ύ Comparing two name lists in Excel
Sign-ups against payments, last year against this year β finding who is missing and who is on both. Here is how with plain Excel.
1 β The fastest way: one COUNTIF line
Next to list A, put this in an empty cell and drag it down:
A2 is the name to check; B:B is the column holding the other list. Rows that say missing are people not on the other list.
Do it the other way round too. Looking from one side tells you half the story β βin A but not in Bβ and βin B but not in Aβ are different people.
To see only those rows: select the column β Data β Filter β tick only βmissingβ.
2 β If you just want to see it in colour
Shared names light up. Quick β but it does not count them for you.
3 β Why it usually says βno matchβ anyway
To your eye they are the same name; to Excel they are not. It is nearly always one of these four:
| Cause | Example |
|---|---|
| An invisible space | John Smith with one space at the end β the most common |
| Spacing inside the name | Mary-Jo β Mary Jo β MaryJo |
| Brackets or titles attached | John Smith (Year 3) β John Smith Β· Dr. Lee β Lee |
| Numbers stored as text | Student ID 20250001 is a number on one side and text on the other |
Clean first
Compare the cleaned columns and far more will match. Then turn the cleaned results into values (Copy β Paste Special β Values), or sorting will scramble them.
4 β What is left: things only a person can tell
- Two people, same name β two John Smiths in one class. A name alone cannot separate them.
- One letter different β Catherine β Katherine. Typo or another person? A machine cannot decide.
- A changed surname β marriage, a name change.
From here a person has to look. That is why a good tool does not quietly merge things β it puts the look-alikes side by side so you can choose.
We sell a small Windows program: give it two lists and it separates who is missing and who is on both. It does the cleaning above for you, and anything that still does not match is shown side by side β βare these the same person?β β so you decide. It never merges on its own.
No internet needed, nothing leaves your computer. A monthly subscription you can cancel any time.
π Smart List Compare β $1.99 / monthWhere this comes from
Formulas work in any recent version of Excel; in some regions Excel uses ; instead of , between arguments.
Everyoneβs situation is different. The numbers here are examples β swap in your own.
VALUES is a small shop that makes tools to take work off your desk Β· More guides Β· The tool shelf Β· Bookshop Β· Free downloads Β· Seller information