Create Unique ID from information found in other cells (abbreviations and incremental number)

[PTHops]

Hi there. I am stuck on developing a unique ID for staffed positions that do not automatically get assigned one from or organizational charts. For example, students, casuals and assignments do not get a unique ID.

Column C is where I want the new position number that is a merge of Column B and part of Column E. Pretty simple for Student and Casual, I imagine, however I really don't want the abbreviation of Assignment to be "***".

ALso need the "-00" to be a unique number and incremental if there is an existing Column B and Column E combination.

Advice, please, including a possible formula. Much appreciated! I need this so I can do Power BI analyses on a massive number of positions.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=B2&"-"&SUBSTITUTE(LEFT(E2,3),"***","AGN")&TEXT(COUNTIFS(B$2:B2,B2),"-00")

 

[PTHops]

Sorry - I am using Microsoft 365 and the file is stored in SharePoint.

Your formula is a great help so far. I think I need a False statement somewhere. The formula works great for rows 2 to 5, but you can see "***" came up (my fear) and the foruma replaced the unique ID that was assigned in row 7 (hence needing a false?). I can't tell you how much I appreciate your help!

 

[Fluff]

The *** in the formula needs to be replaced with the 1st 3 letters of ASSIGNMENT.

 

[PTHops]

Perfect! I will try to work out the false part so the formula doesn't replace position numbers already assigned by our Org Chart.

 

[Fluff]

A cell cannot contain both a value & a formula. So if you already have values in that column, then you just put the formula in the empty cells.

 

[PTHops]

Replied too soon. I have lost the sequential numbering.

 

[Fluff]

You need to change the two B6 references to B2