How to Check for Duplicate ItemIDs in Excel


Uploading data in the Webmanager or via Dropbox will fail if there is a duplicate ItemID in the file to provide.


Let's assume that the actual product data starts in A2, where column A is "ItemID"  in the Excel sheet.

 In an adjacent column, include a formula like this: =COUNTIF(A:A,A2) Then drag it down.

When the formula is dragged down, it will count the quantity of instances of each ItemID and will also check that there are not leading or trailing spaces which might mess up that comparison.

For example: 'Green ' and 'Green' and 'Green   ' are all different but will import as 'Green'

You can use Data>Filter>AutoFilter on the formula column to make sure that there should not be more than one instance for any ItemID. If you see duplicates, you can resolve them by removing the duplicates manually. But what if there are a ton duplicates?


How do you find duplicates when an ItemID can be in multiple categories and you get a duplicates error in a product Categories file upload?


In Excel, combine the ItemID and the Categories column in an adjacent Column

This example has a formula in D2 that also removes spaces from the ItemID and the Categories field and combines them: =TRIM(A2)&TRIM(B2) Drag that down in Column D.

Column E has the same COUNTIF formula that was used previously, but it is adjusted to look at the content in D2 and dragged down. It does not need the TRIM function in it, because we have already removed all spaces in A and B in the formula found in D2.

This formula in E2 will be =COUNTIF(D:D,D2) and then dragged down

Again, look for any number larger than 1 and you will have found a duplication in the combination of the ItemID and Categories.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.