Looking to turn Predecessors cell into a comma delimited list without "FF", "FS", "+x d " etc.

SOLUTION FOUND!

- While, I have not managed to find a way to determine the list of Successors on the Row itself (i.e. have a "Successors" column similar to the Predecessors column),  I have found a way to dynamically determine the list of successors for a given Row number )i.e. I have to manually enter the row ID that I am interested in analyzing) and the list will be calculated. 

Below are the instructions on how to  accomplish this (Requires multiple  columns), multiple formulas  which can be hidden once set up).   A snapshot of the sheet is also attached. 

I hope this is helpful to other smartsheet users.  It is not a perfect solution, but atleast provides a means of identifying a row's successors/dependents !

To Generate a list of Successors for a given Row:

STEP 1)Create  a column named "ROWNUMCounter". IN row 1 of this column, enter:

  "=1+0" 

Then drag this formula down to the end of your sheet.

STEP 2)Create a column named "ROW_ID. IN row 1 of this column, enter

 " =COUNT(ROWNUMCounter$1:ROWNUMCounter1)" 

 - Then drag this formula down to the end of your sheet. - You should see that the values in this column match the row number at the far left of your sheet.    Steps 1 and Steps2 survives addition and deletion of rows. ( i.e. auto-updates).

STEP 3) The formulas hence forth assume that  your "Predecessors" (Dependencies)" Column is called "PRED". (Column type is"Predecessors").  

To Note: Regardless of how the predecessors are entered, I have noticed that a space is added after a comma when there is a list.  I have also  noticed that if a lag or lead is added after a predecessor with FS dependencies,  the "FS" is added automatically even if it was not entered by the user.)    The formulas I use are taking advantage of this smartsheet design that is associated with a Predecessors Colulmn type.  IF this design changes, then the subsequent formulas need to be reviewed.

As an example,  we will assume that  Row 2 of the PRED column has the following:  

" 5, 23FF, 29SS, 15SF, 19FS + 1d,  14SF - 3w"

STEP  4) To the Right of the PRED Column, create the following set of columns, all being  of column type: Text/Number

• PRED-F
• PRED-S
• HasSucessors
• RowID2Find
• Successors

STEP 5) In Column "PRED-F", row 1, enter the following:

=IF(FIND("F", PRED@row) > 0, ", " + SUBSTITUTE(PRED@row, "F", ", ") + ",", ", " + PRED@row + ",")

Copy this formula down to the end of your sheet.

This removes  any "F"s that are listed in the PRED column and replaces it with a ", " (comma followed by a space).  The space is there to allow for Column wrapping should the list of predecessors be long).  In addition,  the contents of the PRED" column, are wrapped with two commas (one in the front and one in the end...so that the search  string used in "HasSuccessors" will work for all numbers, regardless of whether they are at the beginning, middle or end of the string.

Example:

 " 5, 23FF, 29SS, 15SF, 19FS + 1d,  14SF - 3w"   becomes 

 " , 5, 23, , , 29SS, 15S, , 19, S + 1d, 14S, -3w, "

STEP 6)  In Column "PRED-S", row 1, enter the following:

=IF(FIND("S", [PRED-F]@row) > 0, ", " + SUBSTITUTE([PRED-F]@row, "S", ", ") + ",", ", " + [PRED-F]@row + ",")

Copy this formula down to the end of your sheet.

This removes any "S" that are listed in the PRED column and replaces it with a "," (comma followed by a space).  The space is there to allow for Column wrapping).  In addition,  the contents of the PRED" column, are wrapped with two commas (one in the front and one in the end...so that the search  string used in "HasSuccessors" will work for all numbers, regardless of whether they are at the beginning, middle or end of the string.

Example

 " , 5, 23, , , 29SS, 15S, , 19, S + 1d, 14S, -3w, "    becomes:

 " , , 5, 23, , , 29, , , 15, , , 19, , +1d, 14, , -3w, , "

STEP 7) In Column "HasSuccesors", Row 2 and onwards,  enter the following:

=IF(ISBLANK([RowID2Find]$1), "No ROW ID identified", IF(FIND(", " + [RowID2Find]$1 + ",", [PRED-S]@row) = 0, 0, [ROW_ID]@row))

This needs to be copied down to the rest of the sheet.  oOte, that I enter this starting at row 2, as I  will be using Row 1 as an additional Row Header.  

This Formula, is first going to check if there is a value entered in "RowID2Find", Cell1.  If not,  it  returns "No ROW ID indentified".  Otherwise,  it will search for the value in the PRED-S column for that row.  IT is looking for the number to be surrounded by a comma  followed by a space on the left, and a comma on the right.  If it doesn't find the pattern in that row of the the PRED-S column, then it returns a 0, otherwise it returns the row ID. 

 This specific pattern search ( , RowID2Find, ) ensures that a false positive is not identified when the number in question appears as part of another predecessor (e.g. Looking for 1 in a row where the predecessors are (, 19, 214, 21,)  (The "leads and lags" do not matter in the PRED -S column because they will never match the pattern being searched.

STEP 8) In row 1 of  Column " ROwID2FIND",  enter the ROW ID that you are interested in finding the list of successors for.   The rest of the column's cells are not used. 

STEP 9) The last step is to collect all of the rows that have the row ID identified and display them as a comma delimited list.  To do  this: 

In Row 1 of the "Successors" column, enter the following:

=IF(ISBLANK([RowID2Find]$1), "No Row number identified", IF(SUM(COLLECT(HasSuccessors:HasSuccessors, HasSuccessors:HasSuccessors, >0)) > 0, JOIN(COLLECT(HasSuccessors:HasSuccessors, HasSuccessors:HasSuccessors, >0), ", "), "no sucessors"))

There is no need to drag this formula down to the rest of the cells in the column.

This formula is first checking to make sure that the ROwID2Find, Cell 1 is not empty.  If it is, then "No Row Number identified" is listed. If ROWID2Find$1 is populated, then, check to make sure that the "Has Successors" column has atleast one cell with a value other than 0 (i.e. the row has atleast 1 successor).  If the  Row in question, has atleast 1 successor, then  collect the list of successors and seperate them with a comma. Otherwise, enter "No Successors"

NOTES:

Unfortunately, I have not figured out how to statically list the successors for a given column.  One must dynamically enter the row ID to be analyzed in ROWID2Find$1.

I find that it is useful to have the same sheet displayed on two  windows/screens - One, where I can always see the header Row 1 and the other sheet to  search through the rows as per the list generated in Successors$1. (This basically saves me from having to scroll back and forth in a large sheet with hundresds of rows)   In doing so, one has to remember to save and refresh  in order to see changes take place as the PRED column is updated, rows are added/deleted etc.

Smartsheet Community Mentions:

In order to come up with this solution, I used the Smartsheet help, but also looked to the Smartsheet Community for assistance.  Parts of the final solution were borrowed from input provided by the following two contributors:

1) Rob Hagan for his solution on autopopulating a ROW_ID column that is updated as rows are inserted/deleted.   https://community.smartsheet.com/discussion/formula-determine-row-number 

2) Lee T. : In trying to figure out how I can search a predecessor cell that is comma delimited, I came across a formula shared by Lee T.    I used it as the base for the formula in my "Has Successors" column.  https://community.smartsheet.com/discussion/can-successors-column-be-displayed )