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
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!