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.

Answer 1

Try:

Formula in A2:

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

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