Conditional format of duplicate values

Olli Matikainen

I'm looking to apply conditional format to show duplicate values within a column - similar to conditional formatting in Excel (you can choose to apply specific format to either unique or duplicate values).

I don't see this functionality in Smartsheet - or am I missing something?

I've submitted an enhancement request already.

If anyone has an easy way to filter out duplicate values in a column, would be appreciated...!

Paul Newcome

@Andrée Starå: Misplaced parenthesis... 😉

=IF(COUNTIF(ColumnName:ColumnName; ColumnName@row) > 1; 1; 0)

=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)

Andrée Starå

Hi Olli,

I do! 😉

We will need to add a so-called helper column to make it work, and then we’d use something like this in a helper flag type column.

=IF(COUNTIF(ColumnName:ColumnName; ColumnName@row > 1); 1; 0)

The same version but with the below changes for convenience.

=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row > 1), 1, 0)

Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

Did that work?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@Andrée Starå: Misplaced parenthesis... 😉

=IF(COUNTIF(ColumnName:ColumnName; ColumnName@row) > 1; 1; 0)

=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)

Andrée Starå

@Paul Newcome

https://media.giphy.com/media/l1J3Psr9HDSvRThsY/giphy.gif

Paul Newcome

@Andrée Starå 🤣🤣🤣

cvarela

Hi all,

How do you use Paul's formula below to look for duplicate values for the entire content of the cell?

For example, I applied this formula but have a cell with the value 333 that is being flagged because there is a cell with the value 151333. How can we make sure the full number is considered in the countif? Or is there a better formula to identify duplicate values when the numbers may exist within a larger number?

=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)

Paul Newcome

@cvarela Which are you looking for?

This should only flag if the number is an exact match:

=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)

This should flag if the number is found anywhere within another number string or is an exact match:

=IF(COUNTIF(ColumnName:ColumnName, CONTAINS(ColumnName@row, @cell)) > 1, 1, 0)

If neither of those are working for you, are you able to provide some more examples of exactly what you need and possibly some screenshots?

cvarela

Thank you for your response. After further review of the extensive data, I found the exact match 🤦‍♀️

Genevieve P.

Jumping in here just to say thank you for reviving this thread... I had a genuine chuckle out-loud at @Andrée Starå's gif from 2020.

Paul Newcome

@cvarela Happy to help. 👍️