Conditional format of duplicate values
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...!
Best Answer
-
@Andrée Starå: Misplaced parenthesis... 😉
=IF(COUNTIF(ColumnName:ColumnName; ColumnName@row) > 1; 1; 0)
=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@Andrée Starå: Misplaced parenthesis... 😉
=IF(COUNTIF(ColumnName:ColumnName; ColumnName@row) > 1; 1; 0)
=IF(COUNTIF(ColumnName:ColumnName, ColumnName@row) > 1, 1, 0)
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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)
-
@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?
-
Thank you for your response. After further review of the extensive data, I found the exact match 🤦♀️
-
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.
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
@cvarela Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives