Is there a way to create a list out of joined string in a cell?

Jorge Villarreal

I have Sheet A, were I have a column with Task Description that has parents. I created another column where I put the parents corresponding to the child.

Example:

Parent ID .........|........Task Description

[blankcell]........|........Parent 1

[Parent 1].........|............Task 1

[Parent 1].........|............Task 2

[blankcell]........|........Parent 2

[Parent 2].........|............Task 1

[Parent 2].........|............Task 2

On Sheet B I use =JOIN(DISTINCT({Parent ID}), ",") where I get all unique values from the Parent ID column on Sheet A as a string:

[Parent 1],[Parent 2]

What I want to do now is separate those parents into a list so that I can use the information for other metric related formulas. That is where I'm stuck, I don't know how to "unjoin" a string of text inside of a cell.

The goal is to get the names of parents (no duplicates) from Sheet A to Sheet B so that I can use them for formulas. This has to be automated, if there's manual work (adjusting ranges, fixing formulas every week, etc.) then this won't do.

Thanks for taking your time and hope you have a great day!

Jorge Villarreal

Hi Andrée

First of all, thanks for the quick reply.

Unfortunately, this does not work. When going beyond the 2nd parent, the formula stops working. It throws the error "#Invalid Value". I honestly don't know why.

I did a correction to the formula and got the result. I don't know how, honestly. I figured that the structure was ok, maybe a typo was wrong? So I started fiddling with the $ signs to see if I got what I needed.

Fortunately, I got it. But, with the problem that values are now being repeated (example): Parent 1, Parent 2, Parent 3, Parent 1, Parent 1, etc (all on separate columns, which is great).

What I did now is I nested the formula inside an If statement saying that if the value calculated is = to the value from $text@row (the first column), then display "Stop here", if not, continue with the calculation.

The correction I made was here (look for the crossed bold $ sign):

=LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN($B@row:B@row, ",") + ",", "")) - 1))

As you can see, I deleted the $ sign from the second round of the join formula.

The final result with the if statement is:

=IF(LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN(B@row:B@row, ",") + ",", "")) - 1) = $A@row, "Stop here", LEFT(SUBSTITUTE($Text@row, JOIN($A@row:B@row, ",") + ",", ""), FIND(",", SUBSTITUTE($Text@row, JOIN(B@row:B@row, ",") + ",", "")) - 1))

As of right now this is working! If you see a flaw with something I might have done, please contact me, I would greatly appreciate it.