Process Identifier Automation

Pestomania

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)), ""))

Will.Parente

Which part of the number is being repeated?

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

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?