Need to automate unique identifier

[MLK]

I have a file containing resources and a resource could be listed multiple
times. Their user ID is a key for reporting, but of course this is listed
multiple times as well.

To handle this currently, another column was inserted with their ID’s copied
into it. If a user is listed more than once, than a letter is appended to
their user ID to make it unique. For example: smithj is listed on 3 rows
and the ID’s would appear as : smithj, smithjx, smithjy (where the x and y
are appended to subsequent lines).

Is there a way to automate appending a letter to the user ID? Typically a
resource wouldn’t have more than 4 lines, so I’m only looking to append “xâ€,
“y†and “zâ€.

 

[Max]

Assuming source data running in A2 down,

Place in B2
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTIF($A$2:A2,A2)=2,A2&"x",IF(COUNTIF($A$2:A2,A2)=3,A2&"y",IF(COUNTIF($A$2:A2,A2)=4,A2&"z","ID over 4 x")))))
Copy B2 down as far as required

Any IDs appearing more than 4 times will be flagged: ID over 4 x

 

[Biff]

Here's another one:

=IF(A2="","",A2&LOOKUP(COUNTIF(A$2:A2,A2),{1,2,3,4,5},{"","x","y","z","++"}))

Biff

 

[MLK]

Great! Thank you.

Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTIF($A$2:A2,A2)=2,A2&"x",IF(COUNTIF($A$2:A2,A2)=3,A2&"y",IF(COUNTIF($A$2:A2,A2)=4,A2&"z","ID over 4 x")))))
Copy B2 down as far as required

Any IDs appearing more than 4 times will be flagged: ID over 4 x

 

[MLK]

Thanks!

Here's another one:

=IF(A2="","",A2&LOOKUP(COUNTIF(A$2:A2,A2),{1,2,3,4,5},{"","x","y","z","++"}))

Biff

 

[Biff]

Even shorter but assumes no name repeats more than 5 times:

Typically a resource wouldn't have more than 4 lines

=IF(A2="","",A2&CHOOSE(COUNTIF(A$2:A2,A2),"","x","y","z","++"))

Biff

 

[Max]

You're welcome, and thanks for calling back.