Unique ID auto generated from State designation

SCJohnston

I have attached an image of a project schedule. I need to generate a formula for the "task-id" column that auto-generates a unique prefix and suffix based on the "state" column. The formula should reference the two character state and and start at 0001, if this initial encounter of that state. If the state has been encountered previously, add one digit from the previous occurrence and place the result in that cell. I have provided examples in the "task-id" column of what the previous results should be. I generated a formula that should produce the result, but it isn't quite working.

=JOIN([State]@row," - ") + RIGHT("000" + COUNTIFS([State]:[State],[State]@row, ROW():ROW(), <= ROW()@row), 4)

Any suggestions?

prime_nathaniel

@SCJohnston this is not a good use case for formulas, the only way to do this that I have seen before is to try to index each state to the row you are in, this KIND of works but if items are moved up and down the index changes and thus the ID changes which is not something you want ID's doing.