Auto populate project ID based on identifier

GPR · 10/03/23

Is there a way to auto populate a letter and number ID sequence based on an identifier?

I'm trying to create a project ID based on business segment, state and have a number auto generated, but be sequential within the business segment.

Ex:

AB_FL_0001

AB_CA_0002

AB_FL_0003

CD_NY_0001

CD_LA_0002

jessica.smith · 10/04/23

@GPR Apologies, I missed that the number needed to be sequential within the business segment. What about something like this?

Add these columns:

Business Segment - Drop-down with restricted options

State - Drop-Down with restricted options

AutoID - Auto Number system column type

ProjectNumber - Returns the sequential number of the project by counting the total number of times the unique Business Segment and State combo occurs in the list and then substracting the number of times it occurs in the list with an AutoID greater than the AutoID of the row

=COUNTIFS([Business Segment]:[Business Segment], =[Business Segment]@row, State:State, =State@row) - COUNTIFS([Business Segment]:[Business Segment], =[Business Segment]@row, State:State, =State@row, AutoID:AutoID, >AutoID@row)

AddZeros - Used to add places to the project number if that is needed. It works by counting the number of characters in the ProjectNumber column and then returning enough zeros to bring it to 4 places.

=IF(LEN(ProjectNumber@row) = 1, "000", IF(LEN(ProjectNumber@row) = 2, "00", IF(LEN(ProjectNumber@row) = 3, "0", IF(LEN(ProjectNumber@row) > 3, ""))))

ProjectID - include formula to join the 4 columns into a unique project ID

=[Business Segment]@row + "_" + State@row + "_" + AddZeros@row + ProjectNumber@row

Joe Calderazzo · 10/03/23

One option is to use some helper columns where you can create the individual columns for each portion of the Project Number - AB, CD, etc. in one column, FL, CA, NY, etc. in a second column, then create a third column configured as Auto Number with 4 places - then use a JOIN formula to put it all together.

jessica.smith · 10/03/23

@GPR

I would approach by adding the following columns:

Business Segment - Drop-down with restricted options

State - Drop-Down with restricted options

AutoID - Auto Number system column type

ProjectID - include formula to join the other 3 columns. If the columns are positioned together you can use a JOIN function (requires a range), otherwise you can do something like: =[Business Segment]@row + "_" + State@row + "_" + AutoID@row

GPR · 10/03/23

@Joe Calderazzo @jessica.smith I tried both of those previously but what I am struggling with is that the numbers are not sequential for each business segment. They are sequential for the sheet as a whole.

Joe Calderazzo · 10/03/23

Also, you can do this calculation on a separate sheet, then create an automation to copy the row to the sheet where you ultimately want the data to reside. This will convert the JOIN formula to text, but will be editable, so may want to lock the column on the final sheet.

Joe Calderazzo · 10/03/23

Then would likely need separate helper sheets for each type you wanted to differentiate, then run a Copy Row automation to get them from the helper sheets to a single Final sheet.

Joe Calderazzo · 10/03/23

Would you be able to automate a MOVE to the helper sheets to pick up the project number, then copy/move back to the Final sheet? This would likely take multiple Move Automations, one for each helper sheet.

jessica.smith · 10/04/23