Count non-duplicate text cells

TobiasKarlsruhe

Hello everyone 👋,

I want to count the number of non-duplicate 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 non-duplicate 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. 😄

JamesB

@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.

TobiasKarlsruhe

@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?

JamesB

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

TobiasKarlsruhe

@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 2-4 times due to 2-4 assignees in an Assignee column. Does that answer your question?

JamesB

@TobiasKarlsruhe

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.