Detecting the unique items on a list
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!
Help Article Resources
Check out the Formula Handbook template!