Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Append unique number to duplicate cells

Posted on 2012-09-12
Medium Priority
332 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

ID: 38392324
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

ID: 38392371
Thomas,

When I do that, I just get

FRANK -0

for each result
0

LVL 10

Expert Comment

ID: 38392457
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

NBVC earned 860 total points
ID: 38392461
Try simply:

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

LVL 10

Expert Comment

ID: 38392554
(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

ID: 38392561
This worked perfectly.  Thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will diâ€¦
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month10 days, 23 hours left to enroll