Formula for auto number/prefix based on groups

LaureNoel36

I have a sheet with several parent-child hierarchies where I have things grouped by Workstream. I want each task under each workstream to have a unique task ID with different pre-fixes depending on the workstream. Ex: Data tasks would be D1, D2, D3, etc. Organizational Change Management tasks would be OCM1, OCM2, OCM3, etc. I have a "Group" column which has the prefix for each workstream: OCM, B, BPM, T, SC, PC, and D

I attempted to figure this out using Chat GPT and was successful in getting a Group Count formula working to get the unique task number for each workstream. My group count column uses the following formula:

=COUNTIF(Group$1:Group@row, Group@row)

I am now trying to get my Task ID column to combine the Group column with the Group Count column results, so the Task ID would become Group + Group Count result. Chat GPT gave me this formula.

=IF([Group]@row = "OCM", "OCM" & [Group Count]@row,
IF([Group]@row = "B", "B-" & [Group Count]@row, ""))

And then said to repeat the IF statement with all my different group names. I tried that and it comes back #unparseable. I simplified my formula by just trying it with one group with the following formula

=IF([Group]@row = "OCM", "OCM" & [Group Count]@row, "") and then tried

=IF([Group]@row = "OCM", "OCM" & [Group Count]@row)

I have checked for all different errors including extra spaces in the formula, in column names, checked that column names match the formula, etc. Nothing seems to work. Any help in getting me a formula for this last step of the process?

Thank you!

AKnight

Hi @LaureNoel36 !

Oh this question made me super excited because I get to use a formula that I have saved in my notes.

You will need a auto numbering column first. If "Group count" is your auto numbering column then the below formula should work (make sure it is a text column type)

=IF([Group]@row = "OCM", "OCM-", IF([Group]@row = "B", "B-")) + COUNTIFS([Group Count]:[Group Count], @cell <= [Group Count]@row, [Group]:[Group], @cell = [Group]@row)

You will need to add If statements for every prefix option as well.

AKnight

Hi @LaureNoel36 !

Oh this question made me super excited because I get to use a formula that I have saved in my notes.

You will need a auto numbering column first. If "Group count" is your auto numbering column then the below formula should work (make sure it is a text column type)

=IF([Group]@row = "OCM", "OCM-", IF([Group]@row = "B", "B-")) + COUNTIFS([Group Count]:[Group Count], @cell <= [Group Count]@row, [Group]:[Group], @cell = [Group]@row)

You will need to add If statements for every prefix option as well.

LaureNoel36

@AKnight Oh my gosh thank you so much! That worked perfectly!! I will continue to use that formula with my other groups and add to it! You are a lifesaver!

AKnight

Happy to help!