asked on

Append unique number to duplicate cells

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

nutsch

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

jackadmin

ASKER

Thomas,

When I do that, I just get

FRANK -0

for each result

mark_harris231

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)

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mark_harris231

(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.

jackadmin

ASKER

This worked perfectly. Thank you.