Find Value in Column, Copy and Paste Excel 2016

[And180y]

In the attached workbook sheet one has a list of jobs and employees, sheet two has the employees names and the aim is to
automatically have the jobs assigned to that employee displayed in a cell, in the example all Dave's jobs are in J6 (Done manually).
The names in sheet one might not be in the same place all the time as data gets pasted in so and range could be longer but sheet two would be static.

Thanks in advance.

[Fluff13]

Why not just use a formula
In J3 copied down

=TEXTJOIN(",",,FILTER(Sheet1!D:D,Sheet1!G:G=A3))

or, if you dont have dynamic arrays

=TEXTJOIN(",",,IF(Sheet1!$G$1:$G$100=A3,Sheet1!$D$1:$D$100,""))

[And180y]

Thanks Fluff13 works a treat, then I put some formulas back in and now it seems to think the array is empty. (The job numbers get created elsewhere)

[And180y]

I wonder if the mods could move this to the Formulas and Functions section please. :-)

[Fluff13]

Can you supply a workbook that includes your formulae?

[And180y]

Here's the formula that pulls the information in. =OFFSET($A$2,COLUMNS($A5:A5)-1+(ROWS($2:5)-1)*5,0)

[Fluff13]

Can you resupply the workbook?

[And180y]

Thanks, have attached a cut down version.

[Fluff13]

Don't think so, at least I can't see one.

[And180y]

Sometimes, I look stupid. Other times I really am. Sorry forgot to click upload. Done now. :-)

[Fluff13]

Sorry forgot to click upload

we've all done that.
How about
=TEXTJOIN(",",,FILTER(Sheet1!D:D,Sheet1!G:G=A2,"no data"))
You can change no data to just "" or any other message you want, for when there is no matching name.

[And180y]

Genius!!

[Fluff13]

You're welcome & thanks for the feedback.

[And180y]

We are both on same version of Excel so it works perfect but on a Excel 2016 doesn't. Apart from changing the version do you know of a way to make it work? :-)

[Fluff13]

That would really need VBA.
Trying to do it for 2016 with formulae would be pretty horrific.

[And180y]

That was my fear, that it was back to VBA. Any takers?

[Fluff13]

How about

Please Login or Register  to view this content.

[And180y]

Thanks Fluff13, perfect solution. Thanks so much.

[Fluff13]

You're welcome & thanks for the feedback.

[And180y]

Fluff13, I broke your code. I decided just to have it all on one sheet so thought it would be enough to remove the sheet references and it would work. = Fail.
In line 4 below the second .range highlights as an invalid or unqualified reference.

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[And180y]

Thanks Fluff13, it works perfectly only thing is that it's slower than before. Thanks again. :-)

[Fluff13]

If the data is the same size as before, there is no reason why it would get slower, unless you have something else going on.