Help with DISTINCT Function

Chris Russell

I need to create a list of unique names pulled from a column on a different sheet. I used this example to get things generally working. However, I'm not ending up with a truly unique set of names.

The formula in question is: =JOIN(DISTINCT(COLLECT({Fruit}, {Fruit}, @cell <> "")), ",") + ","

Here's an example of how my source data is setup. It is all in one column

COLUMN NAME: FRUIT

Row 1: Apple, Banana, Grape, Pear

Row 2: Grape, Pear

Row 3: Apple, Strawberry, Cherry, Grape

Resulting list:

Apple

Banana

Grape

Pear

Grape

Pear

Apple

Strawberry

Cherry

Grape

So I must be misunderstanding how the DISTINCT function works here. I suspect this is related to the fact that each cell has multiple values in it and maybe the original formula is intended for a column where each cell only has one value in it. Can anyone enlighten me on why this is not working as I expect, and if there is a way to dynamically create a unique list from a column where cells may have multiple values in them?

David Joyeuse

Hi @Chris Russell

Your formula is written correctly, so it should be working fine.

Which rise the question about how your fruit column is set up?

Is it a string text column or is it a multi select drop down list? (Is it created out of a JOIN(COLLECT()) formula)?

If it's a string text column, then Collect will grab all those values as 1 item, and not a list of items. So DISTINCT will see all theses strings as distincts strings of text.

Hope it helped!

Chris Russell

Thanks for the reply @David Joyeuse . This was just an example of what I'm trying to do. The Fruit column, as well as the actual I'm working on are text/number columns.

Any thoughts on how to approach this so that I end up with a list of unique values? I'm not sure how to take the string of values as they currently exist in each cell and get them into an array so that DISTINCT just sees one big list of names to work with.

Chris Russell

I was able to answer my own question. My use of this example was correct and gave me a column with one name in each cell, although many names were duplicates. Then I just had to find a way to create a list of unique names from the column that had duplicates. This post gave me that answer, and now I have one column with a list of completely unique names.