Detecting the unique items on a list

Ekin

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!

Paul Newcome

This should work and wouldn't require that "helper-duplicate" column...

=IF(COUNTIFS([Activity Name]:[Activity Name], @cell = [Activity Name]@row, [Task No.]:[Task No.], @cell <= [Task No.]@row) = 1, 1)

Paul Newcome

How is the [Task No.] column filled in?

Ekin

Task No. is an Auto Number column

Paul Newcome

Do you have any formatting applied to the auto-numbers such as prefixes or anything?

What is the formula populating the helper column?

Ekin

The arrangement for Task No Auto number is as in the picture attached. Helper columns are Text/Number type.

Formulas for helper columns are:

helper-duplicate =COUNTIF([Activity Name]:[Activity Name], [Activity Name]@row)

helper-unique =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))

Paul Newcome

This should work and wouldn't require that "helper-duplicate" column...

=IF(COUNTIFS([Activity Name]:[Activity Name], @cell = [Activity Name]@row, [Task No.]:[Task No.], @cell <= [Task No.]@row) = 1, 1)

Ekin

It worked, thank you very much!

Paul Newcome

Happy to help. 👍️