Solved

# Append unique number to duplicate cells

Posted on 2012-09-12
316 Views
I have a Excel spreadsheet with item names in column C.  I need to add a unique number suffix to each name for any duplicate names in column C.  I can create additional columns to accomplish this.  The new name should appear in column D.

The formula below works to append the total number of duplicates to the end of the name (if greater than one).  However, I need this to append a unique number.

=IF(COUNTIF(\$C\$2:\$C\$2000,C52)>1,C52&"-"&COUNTIF(\$C\$2:\$C\$2000,C52))

For example, what I need is:

COLUMN C
FRANK
FRANK
FRANK
FRANK
FRANK

COLUMN D
FRANK -1
FRANK -2
FRANK -3
FRANK -4
FRANK -5
0

LVL 39

Expert Comment

Try putting this in your second row then copying down. Notice the change in the second countif formula to relative addressing.

=IF(COUNTIF(\$C\$2:\$C\$2000,C52)>1,C52&"-"&COUNTIF(\$C\$2:\$C2,C52))

Thomas
0

Author Comment

Thomas,

When I do that, I just get

FRANK -0

for each result
0

LVL 10

Expert Comment

Thomas's formula works for me if the data starts in row 52 (as per your original formula).  I suspect, however, that your data actually starts in C2?

In which case, just change the instances of C52 in Thomas's formual to C2.

=IF(COUNTIF(\$C\$2:\$C\$2000,C2)>1,C2&"-"&COUNTIF(\$C\$2:\$C2,C2))

(If this works, points to Thomas plz)
0

LVL 23

Accepted Solution

Try simply:

=IF(COUNTIF(\$C\$2:\$C\$2000,C2)>1,C2&"-"&COUNTIF(\$C\$2:\$C2,\$C2),\$C2)
0

LVL 10

Expert Comment

(no points - just providing additional explanation)

@NB_VC -  Good addition with the trailing ",\$C2".

=IF(COUNTIF(\$C\$2:\$C\$2000,C2)>1,C2&"-"&COUNTIF(\$C\$2:\$C2,\$C2),\$C2)

The original formula neglects the fact that there may be only a single instance of a value in the list, which would cause a result of "FALSE" since the 2nd IF action isn't defined.  Nice catch.
0

Author Closing Comment

This worked perfectly.  Thank you.
0

## Featured Post

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.