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

Herwart

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: 

Herwart

Hello jmyzk_cloudsmart_jp

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

Many Thanks! 🙏

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:

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! 🙏