Help with JOIN(DISTINCT(COLLECT formula

Alexis R

I'm currently using this formula: JOIN(DISTINCT(COLLECT({Training}, {Identifier}, [Loc - Employee]@row)), CHAR(10)) in order to pull completed training records for a list of employees. The idea is that if a specific employee has multiple training records, I can use this formula to return these records to compare against their assigned training requirements on another sheet. It works exactly as intended when the training records exist on one row with only one employee being trained. But fails to return anything when there are multiple employees on one training record. Ideally I would like the training record to be pulled so long as the Criterion_Range1 (in my case the {Identifier}) contains the Criterion (in my case [Loc - Employee]@row), regardless if there is another employee within the same cell. Either what I'm asking for is not possible, or I simply can't get my head around creating the proper syntax for that. Examples below:

Training Assigned:

Training Records:

Note that in training record Row 5, Joe and Jorge received Safe Lifting training together but the record was not pulled into the Trainings Completed.

AravindGP

Hi @Alexis R

Try modifying your formula as below

JOIN(DISTINCT(COLLECT({Training}, {Identifier}, CONTAINS([Loc - Employee]@row, @cell))), CHAR(10))

AravindGP

Hi @Alexis R

Try modifying your formula as below

JOIN(DISTINCT(COLLECT({Training}, {Identifier}, CONTAINS([Loc - Employee]@row, @cell))), CHAR(10))

Alexis R

Hi @AravindGP,

This worked, thank you!

I'll be honest, I tried inserting the CONTAINS function there before but couldn't get it to work. I'll have to assume it has something to do with the "@cell" that you chose for the "search_within" parameter. If you don't mind me asking, what exactly does "@cell" do and why does only that work here but not if I try to use {Identifier} again as the "search_within"?

AravindGP

Hi @Alexis R

@cell is looking for a value within the cells in a defined range. Since we are already referencing the range in the collect statement, we do not need to reference the range again.