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 at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
@cvarela Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives