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

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"?

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.