Hi,
I’m trying to create a worksheet that allows people within my team at work to see what it is they are doing at a glance.
With Vlookup I can get the first entries but not the whole list of people, is there a means of me being able to do this?
I am a bit of an amateur when it comes to these things but pick it up quite quickly.
I’ve attached an example so you can have some idea of what I’m doing Example.ods
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:14+02:00:
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
@erAck Thank you for this.
So I can see how that is more effective, is there a means of it populating the name per cell as opposed to all the names listed in one?
It is possible, but may be ugly… I’ll update my answer.