Conditional format of duplicate values

Olli Matikainen · 05/09/20

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 · 05/11/20

@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å · 05/09/20

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 · 05/11/20

@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å · 05/14/20

@Paul Newcome

Paul Newcome · 05/14/20

@Andrée Starå 🤣🤣🤣

cvarela · 02/14/24

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 · 02/15/24

@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 · 02/15/24

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

Genevieve P. · 02/15/24

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 · 02/15/24

@cvarela Happy to help. 👍️

Smartsheet Basics

Smartsheet Basics

Conditional format of duplicate values

Best Answer

Answers

Quick Links

Categories

Trending in Smartsheet Basics

Has anyone tried to create a trivia game using Smartsheet?

What's the maximum number of rows in a sheet ? Is there any limitation on the number of columns ?

Sharing Permission; Shared Sheet Management for Unlicensed User/Free Collaborator