CSV files — like Excel — make it easy to export and import lots of product data. But do you do when your product codes leave you seeing double?

If you receive an error message like the one below, you’ll have to play sleuth and search for the duplicates. 

The error message you receive might state:

# [The primary key value "productcode" was found on more than one line in your file. This must be unique.]

In this example, we'll be using Excel's conditional formatting to detect duplicates within a column. This is most practical for the product table, or more specifically the productcode field though this same technique can be used for any table you want to check for duplicates within. Note: the productcode is the field required to be unique within the product table.

Note that these instructions assume your installation of Excel is set to A1 reference style. If you are set to R1C1 reference style, in place of the below formula, use this instead:

=COUNTIF(C1,RC)>1

The Procedure

  1. In Excel, select column A (so that it highlights the entire column)
  2. From the Format... menu, select Conditional Formatting...
  3. Set the first drop-down to Formula Is.
  4. Paste this formula into the input area next to it: =COUNTIF($A:$A,A1)>1
  5. Set the desired format to indicate a duplicate. Click the Format button. Click the Patterns tab within that dialog box. Click yellow or orange (or some other easy-to-see color). Click OK to close the Format Cells dialog box.
  6. Click OK to close the Conditional Formatting dialog box.

The column will automatically be formatted (i.e., in the background color you selected) for all product codes that are duplicate.

For Excel 2010

  1. Select column A (so the entire column is highlighted).
  2. Under the Home tab, from the Conditional Formatting menu, select Manage Rules.
  3. Click New Rule.
  4. Select Use a Formula to Determine Which Cells to Format from the list.
  5. Set the first menu to Formula Is.
  6. Paste this formula into the Format Values Where This Formula is True field: =COUNTIF($A:$A,A1)>1
  7. Set the desired format to indicate a duplicate. Click the Format button. Click the Fill tab and select an easily recognizable color, such as yellow. Click OK to close the Format Cells dialog box.
  8. Click OK to close the Conditional Formatting dialog box, and then click OK on the next menu screens.

Other Excel Tips

To open a column to the full width needed to see the entire contents: double-click on the vertical bar between the columns in the HEADING row (where you see the A, B,C, etc.)

  • To set the width of any column explicitly, right-click on the column (within the HEADING row), and select Column Width.
  • To insert a new (blank) row before any particular column, right-click on the column in question and select Insert Column.

Summary

Don’t let duplicate product codes get you down in the dumps. Use our tips and tricks above to quickly and easily locate and address dupes within your CSV file. Excel-lent! 😜

Did this answer your question?