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
6
Medium Priority
?
332 Views
Last Modified: 2012-09-12
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
Comment
Question by:jackadmin
6 Comments
 
LVL 39

Expert Comment

by:nutsch
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

by:jackadmin
ID: 38392371
Thomas,

When I do that, I just get

FRANK -0

for each result
0
 
LVL 10

Expert Comment

by:mark_harris231
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Accepted Solution

by:
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

by:mark_harris231
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

by:jackadmin
ID: 38392561
This worked perfectly.  Thank you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question