Joining multiple multi-select entries from different columns into a single cell WITHOUT DUPLICATES

Ryan Stephens

Hello, as above, I am trying to join the data of all cells within the same row into a single cell without duplicates. In the screenshot below I am working with the following formula:

=JOIN(DISTINCT([Column3]2:[Column6]2), CHAR(10))

How can I modify this so that instead of the result being "A B B C" the duplicate entries are removed and the result is "A B C"?

Paul Newcome

How many unique entries could you have across a row?

Ryan Stephens

I think it would be 4 max.

Leibel S

Try the below:

=IF(CONTAINS("A", [Column3]@row:[Column6]@row), "A" + CHAR(10), "") + IF(CONTAINS("B", [Column3]@row:[Column6]@row), "B" + CHAR(10), "") + IF(CONTAINS("C", [Column3]@row:[Column6]@row), "C" + CHAR(10), "") + IF(CONTAINS("D", [Column3]@row:[Column6]@row), "D", "")

Paul Newcome

@Leibel S's solution should work.

Leibel S

@Paul Newcome @Ryan Stephens

A different solution would be if you format the column where you are placing your join into a Multi Select column and run the below function, it will automatically only show the Distinct options. (because a Multi Select can only have each value once).

=JOIN([Column3]2:[Column6]2, CHAR(10))