I have data like this:
| Cage Type |
|---|
| Open House, Open House |
How to make it like this? so I can get a unique list.
| Cage Type | Unique Cage |
|---|---|
| Open House, Open House | Open House |
CodePudding user response：
try:
=INDEX(FLATTEN(REGEXREPLACE(TRIM(QUERY(QUERY(QUERY(SPLIT(UNIQUE(FLATTEN(
FILTER(ROW(A:A)&""&SPLIT(A:A, ", ", )&",", A:A<>""))), ""),
"select max(Col2) where Col2<>',' group by Col2 pivot Col1"),
"offset 1", ),,9^9)), ",$", )))
CodePudding user response：
Use this
=TEXTJOIN(", ",1,UNIQUE(TRANSPOSE(SPLIT(A2, ", ",0))))
Arrayformula
=BYROW(A2:A, LAMBDA(r, IFERROR(TEXTJOIN(", ",1,UNIQUE(TRANSPOSE(SPLIT(r, ", ",0)))),"")))
