Auto-Numbering or Duplicate
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!
Answers
-
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.
-
@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)
-
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:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!