This month's price list and last month's. The customer list in the business software and the one in the invoicing program. Event registrations and attendees. Comparing two lists is an everyday task, and doing it by eye over a few hundred rows is slow and unreliable. With a few formulas you get a precise answer to three different questions: what is missing, what is new, what has changed.
First of all: a common key
A comparison only works if both lists share a field that uniquely identifies each row: a customer code, a product code, an email address, an invoice number. Comparing by first and last name is risky, because an accent, a double letter or a middle name is enough to make two identical rows look different.
If no key exists, you can build one by joining several fields, for example =B2&"|"&C2&"|"&D2, but first check that the combination really is unique.
Clean before you compare
Most false "missing" results come from invisible differences:
- leading or trailing spaces, to be removed with TRIM;
- numeric codes on one side and text codes on the other, for example 1050 versus "1050";
- upper and lower case, which COUNTIF and MATCH ignore but other tools do not;
- leading zeros lost in one of the two lists, "00457" versus 457.
Ten minutes of cleaning save an hour chasing differences that do not exist.
What is in A but not in B
Suppose list A is on the "Old" sheet (codes in A2:A800) and list B on the "New" sheet (codes in A2:A850). On the Old sheet, next to each code, write:
=IF(COUNTIF(New!$A$2:$A$850, A2)=0, "Missing", "")
COUNTIF counts how many times the code appears in the new list: if zero, the entry has been removed. Repeating the same formula on the other sheet, with the ranges swapped, finds the new entries.
Alternatively, with MATCH:
=IF(ISNA(MATCH(A2, New!$A$2:$A$850, 0)), "Missing", "")
The result is identical; MATCH is often faster on very long lists.
With dynamic arrays
In versions that support FILTER, the complete list of missing entries comes from a single formula, in a free area:
=FILTER(Old!A2:A800, COUNTIF(New!A2:A850, Old!A2:A800)=0, "No missing entries")
The result is a list ready to copy into an email or to use as the basis for a check.
What has changed
Knowing that a code is in both lists is not enough: the price or the address might differ. To compare a field, fetch the value from the other list and compare it:
=IF(XLOOKUP(A2, New!$A$2:$A$850, New!$C$2:$C$850, "")<>C2, "Changed", "")
Here column C holds the price. If the code is missing, XLOOKUP returns an empty string, so the row would show as "Changed": to avoid ambiguity, exclude the missing entries first, or combine the two checks in a status column with three values: "Missing", "Changed", "Unchanged".
For calculated numbers, remember to round before comparing: two prices that differ only in a hidden final decimal digit would come out as different.
Duplicates within the same list
Comparing lists assumes each one is free of duplicates on the key. To check:
=IF(COUNTIF($A$2:$A$800, A2)>1, "Duplicate", "")
If duplicates appear, first work out what they mean: they may be errors, or a sign that the chosen key is not unique. Either way, the comparison should wait until they are fixed.
The Data, Remove Duplicates command deletes repeated rows, but it does so permanently and without showing you what it removed. Before using it, work on a copy or highlight the duplicates with the formula above so you can see them.
Highlighting without extra formulas
If you just want to see the differences without adding columns, use conditional formatting with a formula. Selecting the codes in the old list starting from A2:
=COUNTIF(New!$A$2:$A$850, $A2)=0
colours the missing entries red. In some Excel versions the rule does not accept direct references to another sheet: in that case give the new list's range a name and use the name in the rule.
A summary for whoever has to decide
At the end of a comparison a small summary table is useful:
| Outcome | Count |
|---|---|
| Unchanged | =COUNTIF(Status, "Unchanged") |
| Changed | =COUNTIF(Status, "Changed") |
| Removed | =COUNTIF(Status, "Missing") |
| New | count from the other sheet |
In my work this table is the first thing I look at: if the numbers are very different from what was expected, the problem almost always lies in data cleaning or in the choice of key, not in the formulas.
In short
- A reliable comparison needs a unique key shared by both lists.
- Clean spaces, formats and leading zeros first, the cause of false missing entries.
- COUNTIF or MATCH find missing entries; swapping the lists finds the new ones.
- For changes, fetch the value with a lookup and compare it, rounding numbers.
- Check for duplicates before comparing and finish with a summary of the results.
