Gearyforgemore tools

Find what is missing from either list

A lookup formula answers one direction at a time. Load both lists, pick the column they share, and see what is missing from each side at once.

What this catches

  • Records in the first list with no match in the second
  • Records in the second list with no match in the first
  • Records present in both, but with values that differ
  • The same key appearing more than once on either side

You will be asked which column identifies a row. Whichever column both lists share — an ID, code or reference number.

1 Load the two filesCSV or XLSX, one each side

All file data is read entirely within this browser tab. Nothing is uploaded to any server. Closing this tab discards all loaded data. Exports are unlocked by a one-time purchase through Stripe, our payment processor; that happens in a separate tab and never involves your file data. If you unlock exports, a licence token is kept in this browser's local storage; nothing else is stored.

The comparison will appear here: counts for matched, changed, added, removed and duplicate rows, plus every changed cell with its old value beside the new one.

How it works

  1. Load one list into each side. They do not need the same columns or the same row order.
  2. Pick the column the two lists have in common.
  3. Read the removed count for what is missing from the second list, and the added count for what is missing from the first.
  4. Unlock exports if you need to download the result and send it on.

Every row lands in one of five categories

The comparison does not produce a list of differences; it sorts each distinct key on both sides into one of these five, using the column you picked as the key. Rows whose key cell is blank are set aside and listed in the results instead of counted, and a key that appears more than once is reported once as a duplicate - so the totals count distinct keys, not raw rows.

Matched
The key appears on both sides and every compared cell is identical. These are the rows you do not have to look at, and on a healthy comparison they are nearly all of them - which is the point, because the job is finding the handful that are not.
Changed
The key appears on both sides but at least one cell differs. Each differing cell is shown with its previous value beside the new one, so the question is never "something moved" but "this figure was 1,240 and is now 1,420".
Added
The key is on the right-hand side only. Usually a genuinely new record, but it is also what a re-keyed identifier looks like: change a reference and the same row leaves as removed and returns as added, which is worth checking before treating a large added count as growth.
Removed
The key is on the left-hand side only. A record that was there last period and is not there now - a leaver, a cancelled line, a row dropped by a filter that was left switched on when the export ran.
Duplicate
The same key appears more than once on one side. This is reported separately rather than folded into the others because a duplicated key makes the comparison ambiguous: there is no single row to compare against, so totals built on that key are unreliable until it is resolved.

Choosing the key column is the one judgement the tool cannot make for you. Pick a column that identifies a record and does not change between the two files - a reference, a code, an ID. Pick something that varies, such as a name or a date, and rows that are really the same record will be reported as one removed and one added.

Questions

How do I find records missing from one of two lists?
Load a list into each side and pick the column they share. Records with no match on the other side are counted as added or removed depending on which list they are missing from, so both directions are answered at once.
Is this easier than using a lookup formula?
A lookup checks one direction, so finding what is missing from both sides means running it twice and reconciling the results by hand. Here both directions come out of a single comparison, along with the records that exist on both sides but differ.
Do the two lists need the same columns?
No. They only need one column in common to match records on. Columns that exist on only one side are reported so you know they were not compared.
Is my file uploaded anywhere?
No. Both files are read and compared inside this browser tab. Nothing is sent to a server, there is no account, and closing the tab discards everything.
What file formats does it accept?
CSV and XLSX. Both sides can be different formats — a CSV against an XLSX works.
How large a file can it handle?
It has been measured on 100,000 rows a side. Large files take a few seconds to parse, and the comparison itself runs in well under a second.
Can I export the differences?
Comparing and reading the result on screen is free. Downloading it as CSV or XLSX is a one-time unlock, and the price is shown beneath the export buttons once you have a result.

Other tools on Gearyforge