UNIQUE Return Formula

Susan van Niekerk

Hi

I am trying to return a unique word from a Column on my sheet "FinData"

I want to pull unique projects names from the column and create a list of the project names

I have tried a variety of index/match ect and just can seem to get it right

EG:

=INDEX({Project Name}, MATCH(false, ISNOTBLANK({Project Name}), 0))

Bassam Khalil

Hi @Susan van Niekerk

you'll need to use a combination of helper columns and functions to achieve this.Here’s a step-by-step approach:

1-First, you need to create a helper column that marks each project name as unique the first time it appears. Place this formula in the first row of your helper column and fill it down.

=IF(COUNTIF([Project Name]$1:[Project Name]1, [Project Name]1) = 1, "Unique", "Duplicate")

This formula checks if the project name in the current row is appearing for the first time from the top of the column to the current row. If it’s the first occurrence, it marks it as "Unique"; otherwise, it's marked as "Duplicate".

In the first row of this second helper column, use:

=IF([Is Unique?]1 = "Unique", [Project Name]1, "")

Drag this formula down the column. This will list the project name if it is marked as "Unique" and leave the cell blank otherwise.

Now, you can use a collection or reporting feature to display all unique project names. If you want them in a single cell or displayed neatly without blanks:

• You might consider creating a report or using a dashboard widget that only shows rows where the "Is Unique?" column is "Unique". This will effectively filter out duplicates and provide you with a list or report of unique project names.
• Alternatively, you can manually select the non-blank entries from the "Unique Project Names" column to create a list elsewhere in your sheet or in another tool.