A B
1| Bob_ 1, 4, 3, 2, 1
2| Bob 1
3| Bob 4
4| Bob 3
5| Bob 2
6| Bob 1
7| Alice_ 1, 3
8| Alice 1
9| Alice 3
I need to auto-fill the value of for example B1. Which is B2:B6, seperated by a comma. Just like in the example above. The same for B7. But the real sheet is huge, and the rows underneath Name_ is variable.
Is there a formula that can make this easier for me ?
=TEXTJOIN(", ";pCtrl;B2:B6) where pCtrl=0 will cause the creation of a position for each cell of the range even if it has empty text or is blank. pCtrl=1 ignores empty positions.
The function is implemented in V5.2.0 or higher. If the range is replaced by a subformula under array-evaluation there is the bug tdf#99625 which should be fixed as of V5.3.3.
A correspondig TEXTSPLIT function is not yet available.
Editing (also with respect to the comment by the OQ):
As any formula the one suggested above must be adapted to the specific situation, of course.
There is also this demo.
(General Advice: Don’t mix up primary data and results evaluated based on these data in any column.)
B2:B6 would only work for Bob_ though, because Alice_ has fewer ‘children’, and thus the next ‘parent’'s ‘children’ would be included in Alice_'s range
EDIT: nevermind, I found a way to figure it out. Thanks