EXCEL - Is there a formula for TEXTSPLIT from 3 different rows into a sequencing of rows without data text duplication

Question

Column A	Column B	Column C
a, b, c		a
a, c, e		b
b, a, d		c
		d
		e

I can textsplit, but I want the three rows from Column A to join into a single sequencing row in split text without duplication, similar in Column C.

Answer 1

SORT a UNIQUE TEXTSPLIT To Column

Simple

• Be aware of the TEXTJOIN limitation.

=SORT(UNIQUE(TEXTSPLIT(TEXTJOIN(", ",,A2:A4),,", ")))

Practice REDUCE

=SORT(DROP(REDUCE("",A2:A4,LAMBDA(cResult,cCell,
    UNIQUE(VSTACK(cResult,TEXTSPLIT(cCell,,", "))))),1))

A LAMBDA Function

• In the Ribbon, select Formulas->Defined Names->Define Name.
• Under Name, input the function name e.g. SplitDelColumn, under Comment, add a short description, and under Refers to enter the following formula:

=LAMBDA(Data,Delimiter,SORT(DROP(REDUCE("",Data,LAMBDA(cRow,cCell,
    UNIQUE(VSTACK(cRow,TEXTSPLIT(cCell,,Delimiter))))),1)))

• Now you can use it like any other Excel function:

=SplitDelColumn(A2:A4,", ")

• Note that you can do the same with the simple formula.