How can I collect and join IDs of other rows based on the own row ID?

I have the columns "Auto number", "I am following" and "Followed by". I manually maintain the "I am following" column by entering 1 or more IDs of other rows in blank-separated form. Now I would like to fill the "Followed by" column automatically by collecting all IDs of rows in which the respective row ID is mentioned in the "I am following" column.

I have tried to combine the JOIN COLLECT and CONTAINS functions, linke this:

=JOIN(COLLECT([ID]:[ID]; [I am following]:[I am following]; CONTAINS(ID@row, @cell), " "))

But I am failed.

Example:

Find more posts tagged with

Join

collect

Accepted answers

Herwart

Hello jmyzk_cloudsmart_jp

Yes, that was stupid with the semicolon - I guess an old habit from Excel came through ... 😅

Many Thanks! 🙏

All comments

markkrebs

if its multiselect then use a HAS

=JOIN(COLLECT([Row ID]:[Row ID], [Uses Object]:[Uses Object], HAS(@cell, [Row ID]@row)), CHAR(10))

Herwart

does not work completely. I have pasted your formula into my sheet and it looks like this now:

Screenshot 07.11.2023 um 15.42.07 PM [1].png

But in the row of x001 I expect the values x002 and x003. The problem may be, that the value in "Uses Object" is not equal the ID "x001" but contains it.

jmyzk_cloudsmart_jp

@Herwart

Your formula is fine, except you have to use commas (, ) instead of semicolons ( ; ).😅

=JOIN(COLLECT([ID]:[ID]; [I am following]:[I am following]; CONTAINS(ID@row, @cell), " "))

=JOIN(COLLECT(ID:ID, [I am following]:[I am following], CONTAINS(ID@row, @cell)), " ")

If you change the "Uses Object" column to a multi-select dropdown list column, markkrebs's should work.

https://app.smartsheet.com/b/publish?EQBCT=d84e50d285194d79810db4df25e84cc1

Herwart

Hello jmyzk_cloudsmart_jp

Yes, that was stupid with the semicolon - I guess an old habit from Excel came through ... 😅

Many Thanks! 🙏

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.