Identify Duplicate Values On Select Dropdown Selections

MRL

I want to identify any new rows that are trying to use the same filename as an existing row. We do have reuses, so I do not want those flagged. I was able to set up a checkbox helper column for Duplicates that identifies any filename that is repeated in that column.

=IF(COUNTIF([Filename]:[Filename], [Filename]21) > 1, 1, 0) 

If a duplicate filename is used, it flags that row in the Duplicate column.

I then set up a conditional formatting to change the cell color to red for any Filename that has the Duplicate column flagged.

However, I want to refine this based on a Classification column. We reuse files under the same filename, and I do not want those flagged.

I updated the conditional formatting to exclude anything with Reuse in the classification column, but that leaves the original row flagged.

Essentially, I want to flag any duplicate Filename, but only if they meet one of two conditions in a different Classification column: New or No Research.

Any suggestions as to how to get the rest of the way solving this?

SmartLew

=IF(AND(COUNTIF([Filename]:[Filename], [Filename]@row) > 1, OR(classification@row="new",classification@row="New Research")),1,0)

MRL

https://community.smartsheet.com/discussion/comment/344142#Comment_344142

Thank you! This is so, so close to what I need. This was my fault in my explanation.

I essentially want this formula, but a tweak to the constraints. Right now, it's looking for any duplicated Filename, but only flagging the ones that are in "New" or "New Research." What I want is to flag duplicate Filenames that also have the Classification "New" or "New Research".

So if TestA is in the Filename column for a row with a Classification of "New" and one with "Reuse," I don't want them flagged as duplicates.

But if TestA is in the Filename column for a row with a Classification of "New" and one with "New Research," I do want them both flagged as a duplicates.

SmartLew

https://community.smartsheet.com/discussion/comment/344180#Comment_344180

How many classifications do you have? Is it only when a duplicate has Reuse as it's a classification that you dont want both entries flagged?

SmartLew

https://community.smartsheet.com/discussion/comment/344265#Comment_344265

if so;

create a helper column, call it something like "previous classifications"

your formula in this column should be:

=JOIN(COLLECT(classification:classification, Filename:Filename, Filename@row), "-")

This gives you all the classifications for that file name previously

Then in your flag column;

=IF(AND(COUNTIF(Filename:Filename, Filename@row) > 1, NOT(CONTAINS("REUSE", [DUP CLASS]@row))), 1, 0)

This basically says, if theres a duplicate, look at the previous classifications at put them in the same cell. if It contains Reuse, then don't flag them.

See below outcome:

Does that work?