TEXTJOIN no duplicates no blanks, table references

Question

I have this table:

on Cell A4 i want the value "Orange,Banana,Peach,Mango,Melon,Watermelon,Apple".

this is the requirements i have:

table format (so instead of cells like B2,C2,etc - I need [@Fruit 1], [@Fruit 2], [@Fruit 3], [@Fruit 4], [@Fruit 5], [@Fruit 6], [@Fruit 7], [@Fruit 8])
I have more than 8 Fruits normally, i have like 40, for the sake of the example i showed only 8.
no duplicates - if a fruit returns twice show it only once.
no blanks

any help will be greatly appreciated.

I would get the list of fruits then use unique() to get a unique list, after that you could sort alphabetically and the ampersand (&) will join cell contents. — Solar Mike, Aug 21 '22 at 07:58
Hi Mike, what if it's more difficult to get the list of fruit ? — Kobe2424, Aug 21 '22 at 08:10

score 1 · Accepted Answer · answered Aug 21 '22 at 08:48

1

Try:

Formula in A2:

=TEXTJOIN(", ",,UNIQUE(Table1[@[Fruit 1]:[Fruit 8]],1))

This will autofill the rest of 'Fruits' down to A4.

answered Aug 21 '22 at 08:48

JvdV

1 Answers1