How to return unique values listed in a row on excel

Question

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.

Answer 1

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))

Answer 2

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: Split a string (cell) in Excel without VBA (e.g. for array formula) for other alternatives.

You can use UNIQUE(array,[by_col]) with by_col input argument to select unique elements by columns.

Similarly you can use SORT(array,[sort_index],[sort_order],[by_col]) and in a similar way to force sort by column using by_col input argument.

If we have the input argument on cell A1 and you put the following formula in C2:

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

Here is the output in column format returning an 1x5 array:

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.