Guide
How to find duplicate product codes in a price list in Excel
A duplicate product code in a supplier's list is a small thing that causes large mistakes. A lookup returns whichever copy it finds first. An import into your inventory system either rejects the file or overwrites one price with another. And if the two copies have different prices, you will not know which one the supplier meant until you ask. Find them before you do anything else with the list.
Method 1: highlight them (fastest look)
- Select the product code column.
- Go to Home, Conditional Formatting, Highlight Cells Rules, Duplicate Values.
- Pick a fill color and click OK.
Every code that appears more than once is colored. To see them together, filter the column by color: click the filter arrow on the header, then Filter by Color.
Two things to know. Excel's duplicate check ignores letter case, so "abc1" and "ABC1" are highlighted as duplicates even though many systems treat them as different codes. And it compares the cell's value, so a code stored as the number 123 and a code stored as the text "123" may or may not be flagged depending on the version. Highlighting is a first look, not proof.
Method 2: count them (the one to trust)
Add a column next to the codes. If codes are in column A with a header in row 1, put this in B2 and copy it down:
=COUNTIF($A$2:$A$5000, A2)Adjust 5000 to cover your data. Any row showing 2 or more is a duplicate, and the number tells you how many copies exist. Filter column B for values greater than 1 and you have the full list, with the rows still in place so you can see the prices side by side.
COUNTIF has one quirk worth knowing: it treats the criteria as text, so a code that looks like a number with 16 or more digits can be counted as equal to another long code that shares the first 15 digits. For barcodes that long, use this exact-match version instead:
=SUMPRODUCT(--($A$2:$A$5000=A2))Method 3: remove them (only after you have looked)
Data, Remove Duplicates deletes every copy after the first. Microsoft's own advice is to copy the data to another sheet first, because the removal is permanent. There is a more important reason to wait: Remove Duplicates keeps the first row and drops the rest without telling you whether the dropped rows had different prices. If a code appears twice at 10.00 and 12.00, you keep one of those prices by luck.
So the safe order is: count first (method 2), review the rows where copies disagree, decide with the supplier which row is right, and only then remove.
Why duplicates appear in the first place
- The supplier lists the same product under two pack sizes or two units, with the same base code.
- A subtotal or "continued" row repeats a code.
- Two spreadsheets were pasted together and overlap.
- A code lost its leading zeros in one section of the file, so "0123" and "123" both exist. That is a data problem, not a true duplicate. See how to keep leading zeros.
Blank codes are the same problem
A row with a price and no code cannot be matched to anything. Filter the code column for blanks and look at those rows too. They are usually headings, notes, or a product the supplier forgot to code.
What the price list checker does with duplicates
The price list checker on this site does not remove or guess. When a code appears more than once in either file, it lists the rows in a "Duplicate ID" group with the row numbers from both files, and it leaves those rows out of the price comparison. Rows with a price but no code are listed as "Missing ID." That way the summary counts are honest, and the rows a person needs to decide about are at the top of the report.