Need to automate unique identifier
 

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 IDs 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 IDs 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 wouldnt have more than 4 lines, so Im only looking to append €œx€�,
€œy€� and €œz€�.

Need to automate unique identifier
 

Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTI F($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
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 IDs 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 IDs 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 wouldnt have more than 4 lines, so Im only looking to append €œx€�,
€œy€� and €œz€�.

Need to automate unique identifier
 

Here's another one:

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

Biff

"Max" wrote in message
...
Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTI F($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
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".

Need to automate unique identifier
 

Great! Thank you.

"Max" wrote:

Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTI F($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
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 IDs 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 IDs 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 wouldnt have more than 4 lines, so Im only looking to append €œx€�,
€œy€� and €œz€�.

Need to automate unique identifier
 

Thanks!

"Biff" wrote:

Here's another one:

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

Biff

"Max" wrote in message
...
Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTI F($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
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".

Need to automate unique identifier
 

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

"Biff" wrote in message
...
Here's another one:

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

Biff

"Max" wrote in message
...
Assuming source data running in A2 down,

Place in B2:
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,IF(COUNTI F($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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
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".

Need to automate unique identifier
 

You're welcome, and thanks for calling back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"MLK" wrote:
Great! Thank you.