Return list of values in a column if another column is not empty

Question

Hello!I am trying to create a list for "project allocations". So I have a table with all or staff members in column A and in row 1 all our projects. In the cells in between, if a staff member is assigned to a projects there is a value for their time invest in this project, e.g.In another sheet, I want to list all projects and underneath each project list all staff members that are assigned to this client, e.g. spend time on it, so where there is a value for them in the client column in sheet 1.&nbsp;Long term I want to add more about functions etc., but I am already struggeling with the first one. I tried "filter" and also if project column is &lt;&gt;" " but if it does anything at all it always just returns everyone in the staff list.&nbsp;Does anyone have any idea what function would work best? Thank you!&nbsp;&nbsp;&nbsp;&nbsp;

martin_angosto · Answer

Meerande&nbsp;&nbsp;Hi, please see attached document with proposed solution. Let's see if that is what you were expecting.&nbsp;Martin

peterbartholomew1 · Answer

Meerande&nbsp;This builds upon&nbsp;Martin_Angosto&nbsp;'s solution to return the results as a single dynamic array"Worksheet formula (presentation)"
= LET(
    lists, REDUCE(SEQUENCE(3), project, Assignedλ),
    VSTACK(project, DROP(lists,,1))
  )

"LAMBDA function (calculation)"
Assignedλ
= LAMBDA(acc, proj_r,
    LET(
        h, XLOOKUP(proj_r, project, hours),
        HSTACK(acc, EXPAND(FILTER(names, h &gt; 0),3,,""))
    )
);

meerande · Answer

Oh wow, exactly that. Works! I love this forum. Never would have been able to make this up myself, thank you! I'll work on my skills to be just as helpful to someone else at some point 😄

martin_angosto · Answer

Meerande&nbsp;&nbsp;Glad it worked!

martin_angosto · Answer

PeterBartholomew1&nbsp;&nbsp;Very interesting, as always…&nbsp;Cheers,

Forum Discussion

Return list of values in a column if another column is not empty

5 Replies