Need to automate unique identifier

MLK · 08-21-2006, 07:20 PM

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 · 08-22-2006, 01:55 AM

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
--
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â€.
>

Biff · 08-22-2006, 02:45 AM

Here's another one:

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

Biff

"Max" <demechanik@yahoo.com> wrote in message
news:0FD7163E-1BBE-45B9-AD27-13F6DDFB669C@microsoft.com...
> 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
> --
> 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".
>>

MLK · 08-22-2006, 09:25 AM

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

MLK · 08-22-2006, 09:25 AM

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" <demechanik@yahoo.com> wrote in message
> news:0FD7163E-1BBE-45B9-AD27-13F6DDFB669C@microsoft.com...
> > 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
> > --
> > 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".
> >>
>
>
>

Biff · 08-22-2006, 02:20 PM

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" <biffinpitt@comcast.net> wrote in message
news:%23zYa4XbxGHA.560@TK2MSFTNGP05.phx.gbl...
> Here's another one:
>
> =IF(A2="","",A2&LOOKUP(COUNTIF(A$2:A2,A2),{1,2,3,4,5},{"","x","y","z","++"}))
>
> Biff
>
> "Max" <demechanik@yahoo.com> wrote in message
> news:0FD7163E-1BBE-45B9-AD27-13F6DDFB669C@microsoft.com...
>> 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
>> --
>> 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".
>>>
>
>