Identifying Duplicate Values through Conditional Formatting

hni

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

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

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.