Finding Status of duplicate entries

KevinMio

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.

KevinMio

Works like a champ - Thanks.

Paul Newcome

Do you have your groups actually labeled with the numbers, or are you using text and you just have "Group 1" for the example?

KevinMio

I put the example together quickly and in the real use case there will be no numbers just "group" and the associated batch number.

ShelbyWarren

@KevinMio Here's one solution:

1. 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"))
2. In your "Notify" column put this formula as a column formula: =IF([Approved Concatenate Count]@row > 1, "Approved")

KevinMio

Works like a champ - Thanks.

Paul Newcome

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

KevinMio

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.