| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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". |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combine data from 2 worksheets using unique identifier | Excel Worksheet Functions | |||
| Unique identifier | Excel Discussion (Misc queries) | |||
| Unique identifier | Excel Worksheet Functions | |||
| How do I create a unique identifier # when open excel file? | Excel Worksheet Functions | |||
| unique identifier for invoices | Excel Worksheet Functions | |||
Linear Mode
