Auto-Numbering or Duplicate

Pestomania

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!

James Keuning

For the [Process Name] [Process Type] [Site Name] problem, you can just create a column formula:

[Process Name]+[Process Type]+[Site Name]

Then you can use that as a sort of hash value to look for dupes. Once you look for dupes you can use COLLECT in a checkbox column to mark the MIN or MAX (add an ID column) of each duplicate set, and only move the marked records, effectively only moving one version of the duplicate.

Pestomania

@Paul Newcome, @Andrée Starå or @Genevieve P. @Mark Poole @James Keuning

So, I have been playing around with my data and am having problems. Once there is a "second" value in the sequence, the records fail. Notice how in row ID 24 and 21, the process identifier goes to #Invalid Column Value. It works for the first record type of each Process Type and then fails once a second version of that process type occurs again.

I have used the "Title" column to return the Parent Process + "Process Identifier"

Formulas used:

Process Identifier:

=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)

Title (which will be the new Process Identifier):

=IF(COUNT(ANCESTORS([Process Identifier]@row)) > 0, INDEX(ANCESTORS([Process Identifier]@row), COUNT(ANCESTORS([Process Identifier]@row))) + " - " + [Process Identifier]@row, [Process Identifier]@row)

Pestomania

So after playing with a lot of it, this is what I came down to:

New Process Identifier Field:

=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))

New "Sub-Process Identifier":

=IF(COUNT(ANCESTORS([Process Identifier]@row)) > 0, [Process Identifier]@row + " (" + INDEX(ANCESTORS([Process Identifier]@row), COUNT(ANCESTORS([Process Identifier]@row))) + ")", 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)))

By doing it this way, each item has its own "unique identifier" and then a second column that provides the unique identifier + the parent: