Detecting the unique items on a list

Hello all,

Here is my problem: I have a list of items and I want to be able to create a list of unique items. I have 2 main columns Task No.(auto number) and Activity Name. I added 2 helper columns: helper-duplicate and helper-unique. The helper-duplicate shows the amount of duplication each row has based on the Activity Name. The formula for the helper-duplicate column is =COUNTIF([Activity Name]:[Activity Name], [Activity Name]@row)

I want helper-unique to flag the Activities that are unique (helper-duplication equals to 1) and flag the rows that has the max Task no if the helper-duplication is higher than 1.

I prepared the formula as =IF([helper-duplicate]@row = 1, 1, IF(AND([helper-duplicate]@row > 1, ([Task No.]@row = MAX(COLLECT([Task No.]:[Task No.], [Activity Name]:[Activity Name], =[Activity Name]@row)))), 1, 0))

However the helper-unique columns turn out 0 for all the rows.

I have tried looking into this in the forums but I could not find an answer.

Thank you for all your help!

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!