Finding Status of duplicate entries
Hello,
I am a bit stumped by what seems like a pretty straightforward requirement.
I have a huge sheet that tracks the status of a series of documents through review cycles. The documents reference a process name and a batch number, which when combined make a unique entry. Each Group of documents on some occasions use the same material (hence repeats of process name / batch number combo as can be seen in the color coded cells). As new groups are added to the sheet, I would like to identify any of the new duplicates that have the status as "approved" in previous entries.
So in the example below, for the Process "bb" and Batch Number "111", I would like an entry made in the Group 2 & Group 3 notify cell for (concatenate bb111) to show approved. Any suggestions would be appreciated.
Best Answer
-
Works like a champ - Thanks.
Answers
-
Do you have your groups actually labeled with the numbers, or are you using text and you just have "Group 1" for the example?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I put the example together quickly and in the real use case there will be no numbers just "group" and the associated batch number.
-
@KevinMio Here's one solution:
- Create a column titled "Approved Concatenate Count" and put this formula in it as a column formula: =IF([Batch Number]@row = "", "", COUNTIF(Concatenate:Concatenate, Concatenate@row) - COUNTIFS(Concatenate:Concatenate, Concatenate@row, Status:Status, "Approved"))
- In your "Notify" column put this formula as a column formula: =IF([Approved Concatenate Count]@row > 1, "Approved")
-
Works like a champ - Thanks.
-
That will flag in every group. Is that ok, or were you still wanting to only flag for groups 2 and 3 (based on the example in your original post)?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It is OK to flag all group, this way when a duplicate is found, the user can go back and view the status of the first entry. Thanks.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!