Hi there!! Alright, I am playing with what I consider a tough project that I am trying to either 1. get a formula to work or 2. get a better layout to do this. We have a table that people fill out a form. There are a few fields, I am thinking are helpful:
[Process Name] (Primary Field)
[Process Type] (Dropdown):
PP - Parent Process
SP - Sub-Process
3P - Third Party
CA - Critical Applications
CC - Critical Contact
CE - Critical Equipment
CR - Critical Resource
VR - Vital Record
First, I am trying to identify if Process Name is a duplicate elsewhere on the table where the following fields are the same: [Process Name] [Process Type] [Site Name]. If all three of these are common fields, merge into one row.
Second, create a unique [Process Identifier] which is LEFT([Process Type]@row, FIND(" - ", [Process Type]@row) - 1) + unique number based on the next Process Type number. So VR-1, VR-2, etc.
Here is what I have been working, but the countifs isn't working:
=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), false)
Third, copy rows over to Table that matches the process type (I am currently using copy rows through automation), but if there are merged rows (because of duplicates), I want it to override the rows in the Table, since it would create another duplicate if not.
Fourth, go to "Business Processes" Table and have a Join field that looks at all tables (listed below) and identify any part where Table[Parent Process] = [Process Name]@row. That Join will allow me to then go backwards and create a "compiled" table of data.
I know this is a lot of questions, but any thoughts would be helpful!
