Why two Excel reports
don’t match.

1. Define what you are comparing.

Start with the question: How many distinct reported serial numbers appear in this shipment file? That is different from counting how many claim rows exist.

• Scope: these two supplied files and the same agreed sample period.
• Key: the serial number identifies the device.
• Counting rule: count each matching shipped unit once.
• Formatting rule: ignore surrounding spaces and letter case for these sample serials.
• Percentage: unique matched units divided by unique shipped units.

For your own reports, also confirm warehouses, statuses, dates, and any exclusions. If a serial can represent multiple legitimate events, define the event key before calling a repeated serial a duplicate.

2. Keep the original identifier.

Import the two CSVs into separate Excel sheets named Shipments and Claims. Put the original serials in column A, with headers in row 1. Preserve that column and add a Checked serial helper column in B.

For this sample, enter the following in B2 on each sheet and fill down through the data:

=UPPER(TRIM(A2))

The claim value " sn-1008 " becomes SN-1008. The quotation marks here show the surrounding spaces; they are CSV delimiters, not part of the imported identifier.

Agree on cleanup rules before applying them. Spaces or letter case may be meaningful in another identifier system. Excel’s TRIM also collapses repeated ordinary internal spaces and does not remove every kind of invisible character. Microsoft explains its limits in the TRIM documentation.

For identifiers such as 00123, import the column as text before Excel converts it to a number. Changing the display format later does not recreate information that was already lost.

3. Check duplicates before counting.

The claims file contains SN-1004 twice. Both rows can find the same shipment, but they still refer to one shipped unit under the agreed rule.

Keep both original rows and flag the repeated entry. For this sample, a helper formula in Claims!C2 can mark the second and subsequent appearance of a checked serial. Fill it down through row 7:

=IF(B2="","",IF(SUMPRODUCT(--($B$2:B2=B2))>1,"Duplicate claim row",""))

Also inspect the shipment key for duplicates. If multiple shipment rows legitimately share a serial, a lookup alone cannot decide which event is relevant. You may need the serial plus an order, date, or another field.

4. Look up the checked values.

With the eight shipment serials in Shipments!B2:B9, put this formula in Claims!D2 and fill down through D7:

=IF(B2="","Missing serial",XLOOKUP(B2,Shipments!$B$2:$B$9,Shipments!$B$2:$B$9,"Not in shipment file",0))

A found serial is returned as the result; otherwise the formula returns the stated exception. The final 0 requests an exact match. The empty-value check keeps a missing identifier visible.

XLOOKUP returns the first matching result. It does not establish that the shipment key is unique or that a claim should be accepted. This formula requires a version of Excel that supports XLOOKUP; it is unavailable in Excel 2016 and 2019. See Microsoft’s XLOOKUP reference for syntax and availability.

Formulas use English function names and commas. Your Excel language or regional settings may use different names or separators.

5. Explain the result and its limits.

The four unique matched serials are SN-1002, SN-1004, SN-1006, and SN-1008. There are eight unique shipped units, giving a 50% matched share.

Dividing six claim entries by eight shipped units would produce 75%, but that answers a different question. The 50% result here measures matching reported serials within these files. It does not establish a defect rate or the validity of a claim.

An unmatched serial needs investigation. It could fall outside the source period, use another identifier, or be missing from an incomplete export. Record the exception and the next question rather than inventing a reason.

Before you share the report

• The two files cover the agreed scope and period.
• Original identifiers are preserved and cleanup rules are documented.
• Blank and repeated keys are visible on both sides.
• The count distinguishes rows, events, and unique units.
• The percentage names its numerator and denominator.
• Exceptions have a clear follow-up, not an assumed explanation.

If you repeat this comparison regularly, the same rules can inform a reusable Excel or Power Query workflow. Start by making the manual result understandable; then make it easier to repeat.