Help with JOIN(DISTINCT(COLLECT formula

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.

Tags:

Best Answer

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Alexis R


    Try modifying your formula as below

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

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer ✓

    Hi @Alexis R


    Try modifying your formula as below

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

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • 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
    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.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!