Ask Your Question

Revision history [back]

With the Date column (B) on sheet '2019' each date appearing just once the data structure is badly designed, using a merged cell over 4 rows doesn't help either. If the date was given for each row (e.g. in B4 =B3, in B5 =B4, in B6 =B5 and then in B8 =B7 and so on) then a formula in Sheet1.D3 to obtain all names for Task 1 could match each individual date using and return the corresponding name using IF(), for which the resulting array can be transformed to a list using TEXTJOIN(), so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter).

You can copy and paste that formula to D7 for Task 2 and then have to edit the name results references from column C to D, so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.D$3:D$66;""))

(again as array formula closing it with Shift+Ctrl+Enter).

With the Date column (B) on sheet '2019' each date appearing just once the data structure is badly designed, using a merged cell over 4 rows doesn't help either. If the date was given for each row (e.g. in B4 =B3, in B5 =B4, in B6 =B5 and then in B8 =B7 and so on) then a formula in Sheet1.D3 to obtain all names for Task 1 could match each individual date using and return the corresponding name using IF(), for which the resulting array can be transformed to a list using TEXTJOIN(), so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter).

You can copy and paste that formula to D7 for Task 2 and then have to edit the name results references from column C to D, so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.D$3:D$66;""))

(again as array formula closing it with Shift+Ctrl+Enter).

With the Date column (B) on sheet '2019' each date appearing just once the data structure is badly designed, using a merged cell over 4 rows doesn't help either. If the date was given for each row (e.g. in B4 =B3, in B5 =B4, in B6 =B5 and then in B8 =B7 and so on) then a formula in Sheet1.D3 to obtain all names for Task 1 could match each individual date and return the corresponding name using IF(), for which the resulting array can be transformed to a list using TEXTJOIN(), so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter).

You can copy and paste that formula to D7 for Task 2 and then have to edit the name results references from column C to D, so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.D$3:D$66;""))

(again as array formula closing it with Shift+Ctrl+Enter).

Update 2019-07-11T20:10+02:00:

To obtain an array of the result and transpose it to columns so that each name is in its own cell you can use this formula, again entered as array formula:

=TRANSPOSE(IF(SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1)<ROWS($'2019'.C$3:C$66);INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

Note that for the other tasks all column C references have to be adapted to column D and so on, so this is really cumbersome. The formula can be shortened using IFERROR() so that only one of the SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1) expressions is necessary, but that also suppresses any error, YMMV..:

=TRANSPOSE(IFERROR(INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

The result spreads across as many columns as there are rows in the cell range $'2019'.C$3:C$66

If always a maximum number of 4 persons (or another fixed maximum) is assigned to one task and you are using LibreOffice of at least release 6.2 then a less complicated array formula using REGEX() is possible:

=IFNA(REGEX(TEXTJOIN(";";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""));"[^;]+";;{1,2,3,4});"")

Note that the text ";" delimiter of TEXTJOIN() here is also used in the "[^;]+" regular expression, it should not occur in a name of a person (hence I chose ; semicolon instead of , comma). The two consecutive ;; parameter separators in the REGEX() function after the regular expression argument are on purpose, the replacement argument is omitted as the original text is to be extracted. Be aware that the , comma separator in {1,2,3,4} is the array column separator, it may be different in your locale or set to something different. Check under menu Tools -> Options -> Calc -> Formula, Separators.

With the Date column (B) on sheet '2019' each date appearing just once the data structure is badly designed, using a merged cell over 4 rows doesn't help either. If the date was given for each row (e.g. in B4 =B3, in B5 =B4, in B6 =B5 and then in B8 =B7 and so on) then a formula in Sheet1.D3 to obtain all names for Task 1 could match each individual date and return the corresponding name using IF(), for which the resulting array can be transformed to a list using TEXTJOIN(), so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))

entered as array formula (close with Shift+Ctrl+Enter instead of just Enter).

You can copy and paste that formula to D7 for Task 2 and then have to edit the name results references from column C to D, so

=TEXTJOIN(", ";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.D$3:D$66;""))

(again as array formula closing it with Shift+Ctrl+Enter).

Update 2019-07-11T20:10+02:00:2019-07-11T20:14+02:00:

To obtain an array of the result and transpose it to columns so that each name is in its own cell you can use this formula, again entered as array formula:

=TRANSPOSE(IF(SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1)<ROWS($'2019'.C$3:C$66);INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

Note that for the other tasks all column C references have to be adapted to column D and so on, so this is really cumbersome. The formula can be shortened using IFERROR() so that only one of the SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1) expressions is necessary, but that also suppresses any error, YMMV..:

=TRANSPOSE(IFERROR(INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

The result spreads across as many columns as there are rows in the cell range $'2019'.C$3:C$66

If always a maximum number of 4 persons (or another fixed maximum) is assigned to one task and you are using LibreOffice of at least release 6.2 then a less complicated array formula using REGEX() is possible:

=IFNA(REGEX(TEXTJOIN(";";TRUE();IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""));"[^;]+";;{1,2,3,4});"")

Note that the text ";" delimiter of TEXTJOIN() here is also used in the "[^;]+" regular expression, it should not occur in a name of a person (hence I chose ; semicolon instead of , comma). The two consecutive ;; parameter separators in the REGEX() function after the regular expression argument are on purpose, the replacement argument is omitted as the original text is to be extracted. Be aware that the , comma separator in {1,2,3,4} is the array column separator, it may be different in your locale or set to something different. Check under menu Tools -> Options -> Calc -> Formula, Separators.

Otherwise, for earlier LibreOffice versions than 6.2 or an arbitrary number of persons assigned to one task, to obtain an array of the result and transpose it to columns so that each name is in its own cell you can use this formula, again entered as array formula:

=TRANSPOSE(IF(SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1)<ROWS($'2019'.C$3:C$66);INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

Note that for the other tasks all column C references have to be adapted to column D and so on, so this is really cumbersome. The formula can be shortened using IFERROR() so that only one of the SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1) expressions is necessary, but that also suppresses any error, YMMV..:

=TRANSPOSE(IFERROR(INDEX($'2019'.C$3:C$66;SMALL(IF(LEN(IF($'2019'.$B$3:$B$66=$D$2;$'2019'.C$3:C$66;""))>0;ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1;ROWS($'2019'.C$3:C$66)+1);ROW($'2019'.C$3:C$66)-ROW($'2019'.C$3)+1);1);""))

The result spreads across as many columns as there are rows in the cell range $'2019'.C$3:C$66