The rows are not always missing. In Excel, two CSV files usually look out of sync because the row order changed, the key format changed, or one file is not showing the same date scope as the other.
That is why row-by-row comparison fails so fast. Financial CSV exports do not preserve meaning by row number. They preserve meaning by a stable key such as transaction ID, payout ID, invoice number, or a date-and-amount combination when no single ID exists.
What "missing rows" usually means
| Symptom | What it usually means | First check |
|---|---|---|
| Everything stops lining up after row 214 | One file sorted differently | Check the match key, not the row number |
| A row appears missing in one file | The key is stored differently | Compare TYPE() and LEN() on both keys |
| The counts differ by a few rows | Filters, blank rows, or different date scope | Check row count and earliest/latest dates |
| One file has extra rows at the top | Header drift or preamble lines | Check where the actual header row starts |
| A row looks missing after import | The record is duplicated under another key format | Check for leading zeros, spaces, or date conversion |
If you treat row position as the comparison unit, one shifted record makes every row below it look wrong. That is not a missing-row problem. It is a comparison-method problem.
Before you touch a formula
Start with the file structure. These checks take less time than rebuilding formulas that were pointed at the wrong thing.
| Check | What to confirm |
|---|---|
| Match key | Which column uniquely identifies the transaction in both files |
| Header row | Whether both files start on the same real header row |
| Row count | Whether one file is shorter before any filtering |
| Date scope | Whether both exports cover the same first and last dates |
| Filters | Whether hidden rows are making one file look incomplete |
| Key type | Whether the key is text in one file and number in the other |
For bank and ledger files, the key is usually not "row 237." It is a transaction reference, statement line ID, payout reference, invoice number, or a deliberate combined key such as date|amount|reference.
If your lookups are already failing because the key formats do not agree, why VLOOKUP returns #N/A on mismatched CSV formats covers the exact type checks and cleanup steps first.
Map columns before you try to match rows
Two CSV files from different systems rarely use the same column names, even when they mean the same thing.
| File A header | File B header | Same meaning? |
|---|---|---|
| Transaction ID | Reference | Yes |
| Posting Date | Value Date | Often |
| Gross Amount | Credit | Sometimes |
| Description | Memo | Sometimes |
| Payout ID | Batch Ref | Yes |
If the headers do not map cleanly, stop there and decide which fields will define the match. Do not push ahead with a lookup that assumes Reference and Description are interchangeable.
The fastest reliable comparison uses:
- One normalized match key in file A
- The same normalized match key in file B
- A status column that marks matched, missing, or duplicate
Normalize the key before you search for missing rows
This is where most false missing rows come from. The transaction exists in both files, but the key is stored differently.
| What you see | File A stores | File B stores | Result |
|---|---|---|---|
| 0001842 | Text with leading zeros | Number 1842 | False missing row |
| 2026-05-10 | Text date | Excel date serial | False missing row |
| INV-7721 | INV-7721 with trailing space | INV-7721 | False missing row |
| REF-0091 | Uppercase text | Lowercase text in a case-sensitive process | False mismatch |
Use the smallest fix that proves the issue:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))for space and hidden-character problems=TEXT(A2,"0")when the numeric key must stay text=VALUE(A2)when the key should become a true number=TEXT(A2,"yyyy-mm-dd")when both files should compare on the same date key
Do not overwrite the raw key first. Build a normalized helper column beside it so you can see what changed.
A small worked example before you scan the whole file
Suppose file A contains these transaction keys:
| File A raw key |
|---|
0001842 |
0001843 |
0001844 |
And file B contains:
| File B raw key |
|---|
1842 |
1843 |
1845 |
If you compare the raw keys, Excel tells you all three rows are wrong. That is not the real answer.
After normalizing the key correctly, the result becomes:
| Normalized key | File A status | File B status | What it means |
|---|---|---|---|
0001842 | Present | Missing | File B lost leading zeros or uses a different key format |
0001843 | Present | Missing | Same issue as above |
0001844 | Present | Missing | Truly absent if no alternate key exists |
1845 | Missing | Present | File B has a row File A does not |
That example shows why missing-row work is really key-normalization work first. Until both files are speaking the same identifier language, the "missing" result is not trustworthy.
Find true missing rows with a reverse check
The fastest way to prove a row is truly absent is to check both directions.
1. Build one cleaned key column in each file
Suppose file A uses column E for the cleaned key and file B uses column J.
2. Mark rows in file A that do not exist in file B
=IF(COUNTIF($J$2:$J$5000,E2)=0,"Missing from File B","Matched")This is better than a row-by-row eyeball test because it ignores order and checks existence by key.
3. Run the reverse check in file B
=IF(COUNTIF($E$2:$E$5000,J2)=0,"Missing from File A","Matched")The reverse check matters. A one-way formula only tells you one side of the difference.
4. Flag duplicates before you trust the result
=IF(COUNTIF($E$2:$E$5000,E2)>1,"Duplicate key","Unique")If the key is duplicated, a row can look missing when the real problem is that the comparison key is not unique enough.
What changes when there is no single transaction ID
Some finance files do not give you one clean key column. Bank CSVs, payout exports, and manually maintained ledgers often force you to build one.
In that case, use a combined key only after you decide which fields are stable enough to trust:
| Weak key | Why it fails |
|---|---|
| Amount only | Duplicates are common |
| Date only | Many rows share the same date |
| Description only | Descriptions change or truncate |
| Better combined key | Why it works better | ||
|---|---|---|---|
| `date | amount | reference` | Keeps the strongest identifier when one exists |
| `date | amount | type` | Useful when references are missing but row shape is stable |
| `payout_id | net_amount` | Useful for grouped settlement files |
Build the key the same way on both files so the two sides produce identical strings:
=A2&"|"&TEXT(C2,"yyyy-mm-dd")&"|"&TEXT(D2,"0.00")Force the date and amount into fixed formats inside the key. If one side leaves the amount as 1250 and the other as 1,250.00, the combined keys will not match even when the rows do.
This is also where one-to-many problems appear. One payout row can represent several sales. One ledger export can split a single bank movement into two accounting lines. If the files are recording the same event at different levels, the row is not missing. The row shape is different.
Separate missing rows from mismatched rows
A file comparison is only useful when it tells you what kind of problem you have.
| Status | What it means |
|---|---|
| Matched | The normalized key exists in both files |
| Missing from File A | The key exists only in file B |
| Missing from File B | The key exists only in file A |
| Duplicate key | The chosen key cannot identify rows uniquely |
| Mismatch on amount/date | The row exists in both files, but a comparison field differs |
This is where people lose time with VLOOKUP. They use one lookup to answer two different questions:
- Does the row exist?
- If it exists, do the other fields agree?
Those are separate checks. Find existence first. Only then compare amount, date, or status columns.
If you need a second pass for field differences after existence is proven, how to compare two Excel lists and find discrepancies is the next step.
The output you should trust
At the end, your sheet should not say only "some rows are missing." It should produce a result you can act on.
| Output bucket | What you should have |
|---|---|
| Matched rows | Count and total of rows present in both files |
| Missing from file A | Exact list of keys absent from file A |
| Missing from file B | Exact list of keys absent from file B |
| Duplicate keys | Separate review list before final comparison |
| Mismatched fields | Rows that exist in both files but differ on amount, date, or status |
That output tells you whether the problem is a real missing transaction, a file-scope difference, or a broken key.
