Find Unassigned Team with JOIN(COLLECT(

leslie.g

[Allocated Team] is the team that the project has said they need and at what allocations they need them, JOIN(COLLECTed from the children of this parent row =JOIN(COLLECT(DESCENDANTS([Employee Name]@row), DESCENDANTS([Start Date]@row), @cell < TODAY(), DESCENDANTS([End Date]@row), @cell > TODAY()), CHAR(10)). [Assigned Team] is the list of employees who have been told they will be working on this project, JOIN(COLLECTed from a different sheet that has a master list of all employees and which projects they have been told to work on =JOIN(COLLECT({Employee Database - Employee Name}, {Employee Database - Project Separate}, [Project Number]@row), CHAR(10)).

I would like for [Unassigned Team] to pull the list of people who have been Allocated by the project (name appears in [Allocated Team]), but have not been told that they will be working on this project (master list of employees projects does not return the project in question).

I have tried multiple JOIN(COLLECT( formulas, but I'm just not sure what I'm missing. I think it's some sort of combination of the two, but I can't get it right.

=JOIN(COLLECT({Employee Database - Employee Name}, {Employee Database - Project Separate}, NOT([Project Number]@row), {Employee Database - Employee Name}, NOT(CONTAINS(DESCENDANTS([Employee Name]@row), @cell))), CHAR(10)) returns #INVALID DATA TYPE.

leslie.g

I ended up making it work by adding a helper column in the Employee Database that pulled the project they were allocated to.

=JOIN(DISTINCT(COLLECT({Employee Database Range 1}, {Employee Database - Project Separate}, NOT(CONTAINS([Project Number]@row, @cell)), {Employee Database - Assigned Projects}, ISBLANK(@cell), {Employee Database - Allocated Projects}, CONTAINS([Project Number]@row, @cell))), CHAR(10)))

leslie.g

I ended up making it work by adding a helper column in the Employee Database that pulled the project they were allocated to.

=JOIN(DISTINCT(COLLECT({Employee Database Range 1}, {Employee Database - Project Separate}, NOT(CONTAINS([Project Number]@row, @cell)), {Employee Database - Assigned Projects}, ISBLANK(@cell), {Employee Database - Allocated Projects}, CONTAINS([Project Number]@row, @cell))), CHAR(10)))