Identifying Duplicate Values through Conditional Formatting

Smartsheet Community Members -

I'm using Smartsheet (Forms) to manage system access requests. Users provide their Username and to which System(s) they need access.

I'm trying to employ conditional formatting so I can easily identify which requests are duplicative based on whether a request already exists for the same Username AND for the same System(s).

Experts of Smartsheet - What is the best way to approach this? IF + COUNTIF?

Cheers & Thanks in advance for your help!

Hni

Find more posts tagged with

Formulas

Accepted answers

Jeff Reisman

Try this in a "Duplicates" column:

=COUNTIFS(Username:Username, <>"", Username:Username, =Username@row, [System(s)]:[System(s)], =[System(s)]@row)

This will result in 1 for unique rows, 2 (or more) for rows with duplicates, and will ignore rows without a username (blank rows.)

Conditional Formatting: Where Duplicates column is greater than 1, make the entire row 24-pt BOLD Type with hot pink highlighter (or whatever floats your boat, LOL)

Jeff Reisman

@hni

Right click on a cell that has the formula. Select "Convert to Column Formula" down at the bottom. Then it automatically is there in that column in all rows and any new rows as they are created.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

All comments

Jeff Reisman

Try this in a "Duplicates" column:

=COUNTIFS(Username:Username, <>"", Username:Username, =Username@row, [System(s)]:[System(s)], =[System(s)]@row)

This will result in 1 for unique rows, 2 (or more) for rows with duplicates, and will ignore rows without a username (blank rows.)

Conditional Formatting: Where Duplicates column is greater than 1, make the entire row 24-pt BOLD Type with hot pink highlighter (or whatever floats your boat, LOL)

hni

Jeff - Thanks! That worked perfectly.

Would you happen to know how to have the formula 'run' any time a new row is added in the "Duplicates" column?

Tried to use automation/workflows and all it did was put an apostrophe before the formula lol. 🤦‍♂️

Re: Hot Pink Highlighting - Done 😆!

Thanks again - Hni

Jeff Reisman

@hni

Right click on a cell that has the formula. Select "Convert to Column Formula" down at the bottom. Then it automatically is there in that column in all rows and any new rows as they are created.

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

Stephen_at_ODOC

Is this seriously the only way to conditionally format a column that has duplicates? Even for the Primary Column (which you would think would have an automatic way of notifying you of a duplicate) -- SmartSheet has no such function?

Jeff Reisman

@Stephen_at_ODOC As far as I know, nothing new has been added for duplicate detection functionality. Keep in mind, the "Primary" column in Smartsheet's world isn't the same as a Primary Key in a relational database, so identifying or preventing dupes isn't built in. The Primary column's purpose is to serve as the place for the data you're most likely to use to identify the data in the rest of the row, or as the key piece of data that ties multiple rows together.

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.