If you ever encounter duplicate item names—especially when working with retail shop items that have  (SKU) numbers and consist of 1000+ items—this guide can be particularly helpful.


To automatically add numbers (1, 2, 3, etc.) to repeated item names in Excel, you can use a formula that counts how many times each name appears in the list and combines that number with the original name.


Here’s how to do it:


1. Set up your data:

  •  Assume your text values are in column A, starting from row 1 (A1, A2, etc.).
  •  In a new column (e.g., column B), you'll place the formula.


2. Enter the formula in the first cell of the new column:

  •    Code:
       "  =A1&IF(COUNTIF($A$1:A1,A1)>1,"-"&COUNTIF($A$1:A1,A1), "")


3. Explanation of the formula:

  •  A1: This refers to the cell containing the text value you want to modify.
  •  &: This is the concatenation operator, used to combine text strings
  •  IF(COUNTIF($A$1:A1,A1)>1,"- "&COUNTIF($A$1:A1,A1), ""): This part handles the logic for adding the number
  •  COUNTIF($A$1:A1,A1): This counts how many times the text value in A1 appears in the range $A$1:A1 (the range is dynamic, as it grows with each row).
  •  IF(COUNTIF($A$1:A1,A1)>1, ... , ""): If the count is greater than 1 (meaning it's a duplicate), the first part of the IF statement is executed
  •  "-"&COUNTIF($A$1:A1,A1): This part concatenates a hyphen "-" followed by the count (e.g., "-1", "-2", etc.).


4. Copy the formula down:

  • Select cell B1 (where you entered the formula).
  • Drag the fill handle (the small square at the bottom right of the cell) down to apply the formula to the rest of the column.