Ask Your Question
1

Vlookup formula help needed

asked 2019-07-09 18:18:52 +0200

kungfufuzz gravatar image

updated 2019-07-09 18:20:11 +0200

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 C:\fakepath\Example.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-07-10 13:14:18 +0200

erAck gravatar image

updated 2019-07-11 20:14:45 +0200

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 ... (more)

edit flag offensive delete link more

Comments

@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?

kungfufuzz gravatar imagekungfufuzz ( 2019-07-11 12:55:26 +0200 )edit

It is possible, but may be ugly.. I'll update my answer.

erAck gravatar imageerAck ( 2019-07-11 19:20:55 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-07-09 18:18:52 +0200

Seen: 63 times

Last updated: Jul 11