I need to list all unique publications, which are listed horizontally:

• Publications | PUB-942575 | PUB-936976 | PUB-936976 | PUB-936976 | PUB-947197 | PUB-947197 | PUB-940212 | PUB-928798

I want to return only the unique values horizontally in a row, like below:

• Publications | PUB-942575 | PUB-936976 | PUB-947197 | PUB-940212 | PUB-928708

If the publications can be sorted alphabetically, that would be even better.

All the formulas I've found work for values listed vertically, so any help form the community would be greatly appreciated.

CodePudding user response：

Simple UNIQUE() function should work for you like-

=UNIQUE(B1:I1,TRUE)

• Second [by_col] parameter TRUE indicates to return unique by column. If you want to add word Publication then may try with HSTACK().

  =HSTACK(A1,UNIQUE(B1:I1,1))

CodePudding user response：

It is not clear from your example if the input is a string delimited by " | " or a column delimiter. In your question there is no Excel version constraint so I am going to assume you can use TEXTSPLIT in case your input is a string delimited by " | ". If that is not the case, check this question:

If you want the result as pipe delimited string, then on the previous result invoke TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) as follow:

=TEXTJOIN(" | ",,SORT(UNIQUE(TEXTSPLIT(A1," | "), TRUE),,,TRUE))

and the output will be a single single string as follow:

PUB-928798 | PUB-936976 | PUB-940212 | PUB-942575 | PUB-947197

as you can see the result is sorted by ascending order.