Identify Duplicate Values On Select Dropdown Selections

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?

Find more posts tagged with

Sheets

Formulas

Conditional Formatting

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.