Unique IDs that combine a field and sequentially increase

Mike Tomei

I'm creating a change management log that allows users to submit change orders via a form. To simplify this question, I would like the user to include the project number in their request (this will populate a sheet column titled "Project Number"), then a formula in the sheet (in another column titled "Change ID") will combine the Project Number with a number that sequentially increases based on the previous change order requests that have already been submitted in that same sheet. So if the first change order request for project number 20012 gets submitted, then the Change ID is automatically created to be 20012-1. Then when someone submits another change request for the same project number, it automatically creates a Change ID of 20012-2.

I've gotten myself quite confused researching this, so I'm hoping someone can help out with a formula for me. Thanks!

Danielle Arteaga

Hi, Mike. Here's a suggestion that I am totally ripping off from @Paul Newcome (who is brilliant at this stuff):

Create a column on your sheet using the Auto-Number format type. Start with number 1.

Then, in your Change ID column, place this formula:

=[Project ID]@row + "-" + IF(COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row) < 10, "00", IF(COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row) < 100, "0")) + "" + COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row)

Should end up with this:

Hope this helps!

Kleerfyre

So do you have the auto number generate column set for this sheet to generate the change ID number? If so, this is an easy one to do. You would just use a formula to combine the auto generated Change ID Number and the Project ID Number that is entered into the Project ID cell. You wouldn't be able to have this in the auto generated column, so you would have to use the auto generated column as a helper column. Formula would look kinda like this:

=[Project ID]@row + "-" + [Auto Generated ID]@row

See if that works for you. Of course you will have to change the Column names in the formula to match yours.

Danielle Arteaga

Hi, Mike. Here's a suggestion that I am totally ripping off from @Paul Newcome (who is brilliant at this stuff):

Create a column on your sheet using the Auto-Number format type. Start with number 1.

Then, in your Change ID column, place this formula:

=[Project ID]@row + "-" + IF(COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row) < 10, "00", IF(COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row) < 100, "0")) + "" + COUNTIFS([Project ID]:[Project ID], @cell = [Project ID]@row, Auto:Auto, @cell <= Auto@row)

Should end up with this:

Hope this helps!

Paul Newcome

@Danielle Arteaga Thanks for the shout-out.

@Mike Tomei That solution is what I personally use quite a bit with some slight modifications. It does provide for the leading zeros (001, 002, etc.), but there are a number of different ways it can be adjusted. I like to include date stamps and have the numbers "reset" on a regular basis (daily, monthly, yearly, quarterly, etc.).

1234yymmdd-###

I have even built in priorities and other variables to help quickly identify some of the important data based on just the id, so if you need any variations (to include stripping those leading zeros to match what you have in your original post), please don't hesitate to speak up.

Mike Tomei

Thanks all. This did the trick!