Process Identifier Automation

Hi,

I have a formula that should be identifying the next number and adding a number to it based on the next number.

What I am struggling with, it is repeating numbers and I cannot get it to stop.

[Row ID] is an autonumber

[Process Type] is a dropdown with

PP - Parent Process
SP - Sub-Process
3P - Third Party
CA - Critical Applications
CC - Critical Contact
CE - Critical Equipment
CR - Critical Resource
VR - Vital Record

[Site Name] is different site names.

[Confirmed Process Number] is the column that "locks" the process identifier through data shuttle because it pastes the current identifier into the Confirmed Process Number.

=IF([Confirmed Process Number]@row <> "", [Confirmed Process Number]@row, IFERROR(IF(AND(COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Process Type]:[Process Type], =[Process Type]@row) = 0), LEFT([Process Type]@row, FIND(" - ", [Process Type]@row) - 1) + "-" + (COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Process Type]:[Process Type], =[Process Type]@row)), LEFT([Process Type]@row, FIND("- ", [Process Type]@row) - 2) + "-" + COUNTIFS([Row ID]:[Row ID], <[Row ID]@row, [Site Name]:[Site Name], =[Site Name]@row, [Process Type]:[Process Type], =[Process Type]@row)), ""))

Answers

  • Will.Parente
    Will.Parente ✭✭✭✭✭

    Which part of the number is being repeated?

  • Pestomania
    Pestomania ✭✭✭✭✭

    I missed this response. If you notice below, it keeps returning to 0 if there is anything else in the list. Look at PP-0, see how it changes back to PP-0 in the bold area?


    PP-0
    SP-14
    CA-13
    CA-11
    CA-12
    CA-10
    CE-0
    SP-21
    SP-22
    SP-12
    SP-13
    SP-10
    SP-11
    SP-19
    SP-20
    SP-17
    SP-18
    PP-1
    PP-0
    PP-1
    PP-2
    PP-3
    PP-0

    SP-0
    CC-0
    CC-1
    CC-2
    3P-0
    3P-1

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭

    Looking at this I think your formula is seeing the items in your Confirmed Process Number column as individual values. So for instance if something is overwriting the formula, then the formula starts over with the counting. Is this formula that you have a locked column formula?

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with! Book time with me here: https://calendly.com/michelle-choate

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!