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?