Count nonduplicate text cells
Hello everyone π,
I want to count the number of nonduplicate values in a column with the text type.
In this case, I'd have a formula such as =COUNTIF in the [Customer Requirement]:[Customer Requirement] range to count the nonduplicate IDs. As you can see in the screenshot above, I don't want header rows (Indent Level < 1) to be included in the sum. In the displayed range, I'd count 4 individual requirements.
What is the =COUNTIF formula to achieve this? I haven't found any discussion in the Smartsheet Community on this request so far.
Every help is highly appreciated. Thank you very much in advance. π
Answers

@TobiasKarlsruhe Create a helper column with the following formula.
If([Indent Level]@row=1," ",COUNTIF([Customer Requirement]:[Customer Requirement],=[Customer Requirement]@row))
Then you can look for any instance where the result is greater than 1.

@JamesB Thank you for your advice.
The calculated value with this formula is way too low. Generally speaking, the sheet we want to use this in is populated with thousands of rows. I'd guess that 70% of the rows are duplicates, so the calculated value with this formula should have four digits, too.
What could I try next?

@TobiasKarlsruhe You are saying that you would have over 1000 instances of the same exact wording in the Customer Requirement row?

@JamesB Assuming there are currently approx. 3000 individual entries in the Customer Requirement column, I'd assume that 70% of them were duplicated (copied and pasted underneath) to split the multiple assignees into the respective newly duplicated rows. That means that we have over 5100 rows now. Per individual entry in the customer requirement column, it is likely that it has been broken out 24 times due to 24 assignees in an Assignee column. Does that answer your question?

Kind of. The formula I have above is strictly looking at an exact duplicate of the wording from the Customer Requirement in the row where the formula is applied, as compared to the entire Customer Requirement column. If you apply this as a column formula in a helper column, then you can filter for any time the result is greater than 1. If you also keep the blanks in your filter, your parent rows should also remain so you can see the duplicates that exist within each parent/child relationship.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!