Compare two lists with duplicates: check occurrence counts

List Reconcile · September 6, 2026

To compare two lists with duplicates, count how often each value appears on each side before removing anything. A value can be present in both lists and still have a different count. A membership check answers whether an ID exists. It cannot tell you whether four occurrences on one side are covered by two on the other.

This distinction caused a specific problem for an Excel user reconciling a client’s invoice. Their internal report could contain a widget ID four times while the client’s report contained it twice. They were working with roughly 10,000 records and considered numbering each duplicate. That is one reported workflow, rather than evidence that every repeated ID is a billing error. The example below uses fictional data.

Start with the question your count should answer

Suppose List A contains dispatch records and List B contains receipt records. Each row represents one recorded event, and the selected column is the item ID. Comparing those columns asks whether the number of recorded events agrees for each ID. It does not compare a quantity column. A row saying quantity=20 still counts as one occurrence of its selected ID.

Before running the comparison, check the period covered by each export. A dispatch file for the whole month and a receipt file ending yesterday can produce correct differences that do not indicate mistakes. Also check whether either system includes cancelled records, returns, or a subtotal row. Check the export filters first. Otherwise you can spend an afternoon investigating differences caused by a date range.

A small example you can check by hand

Paste this into A using one item per line:

Paste this into B:

With exact matching, the expected report is:

Value	Count A	Count B	A minus B
00123	4	2	2
00456	2	2	0
123	1	0	1
00789	0	1	-1

There are seven records in A, five in B, and four distinct comparison values. The sum of the signed differences is two, matching seven minus five. That total is a useful arithmetic check, but it hides which IDs disagree. Equal total record counts would not prove that the individual ID counts agree either.

You can load this fictional CSV pair with the ID columns selected, or download the A shipment file and B receipt file. Both files have headers and retain description columns.

Read the filters as different questions

In Compare two lists, In both returns 00123 and 00456: both appear at least once on each side. Different counts returns 00123, 123, and 00789. This includes values that appear on only one side. Only A returns 123; Only B returns 00789.

Repeated IDs returns 00123 and 00456, including the equal 2/2 repeat. It does not remove any source records. The zero beside 00456 means its frequency agrees. It does not mean the rows carrying that ID have identical dates, prices, descriptions, or other fields. List Reconcile groups one selected value from each row. If the task is to compare every cell or total invoice amounts, these counts are only an initial check.

Do not deduplicate the inputs first. Both copies of 00456 would collapse to one, and the four-versus-two difference for 00123 would disappear. Deduplication makes sense when the question is simply which distinct IDs exist; keep the original export when frequency has meaning.

Keep the records behind each number

For business files, open the complete CSV on each side and select the ID column. The other columns remain available through View rows. For 00123, inspect all four A records and both B records together. Dates or transaction references can help a person decide what to investigate next.

Avoid assigning 00123-1, 00123-2, and so on independently in two unsorted files and treating those labels as confirmed pairs. The second occurrence in A need not represent the second occurrence in B. Sorting by a date also does not establish a relationship when several transactions share that date. Prefer a real transaction identifier supplied by the source system when individual pairing is required.

The guide to keeping original rows in a comparison explains what the source references mean and how to retain the surrounding fields in a report.

If you need to stay in Excel

For text IDs and an exact, case-sensitive occurrence count, put the ID to examine in D2. With list values in A2:A100 and B2:B100, use:

=SUMPRODUCT(--EXACT($A$2:$A$100,D2))
=SUMPRODUCT(--EXACT($B$2:$B$100,D2))

Subtract the second result from the first. Microsoft documents EXACT as a case-sensitive text comparison; SUMPRODUCT sums the resulting numeric values. Use bounded ranges and exclude headers. These formulas assume the identifiers have already been retained as text. They cannot restore zeros or digits lost during an earlier import.

Our Excel comparison guide covers presence tests and other formula choices. Decide on case and whitespace rules before comparing a formula result with an online report; different rules can produce different counts without either calculation being broken.

Export a review queue, then resolve it

Select Different counts and download an Excel report to keep a summary with source worksheets. The download includes every value in the current filter and search, even if the table has several pages. Clear the search field if you intend to export all count differences.

Keep the original exports alongside the report, and record which file was A. Swapping the files reverses the sign: a positive number means more occurrences in A, not automatically an amount owed. Ask the source owner to explain the specific rows before changing a ledger or issuing a payment. Once the source is corrected, export both lists again and rerun the same rules. A fresh report then shows what changed without silently editing the evidence from the earlier run.

Open the list comparison tool → · All guides