Auto populate project ID based on identifier

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

Tags:

Best Answer

  • jessica.smith
    jessica.smith ✭✭✭✭
    edited 10/04/23 Answer ✓

    @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


Answers

  • 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
    jessica.smith ✭✭✭✭
    edited 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
    GPR ✭✭

    @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.

  • 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.

  • 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
    Joe Calderazzo ✭✭✭
    edited 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
    jessica.smith ✭✭✭✭
    edited 10/04/23 Answer ✓

    @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


  • GPR
    GPR ✭✭

    @jessica.smith This worked perfectly!! Thank you so much

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!