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?
-
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)?
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!