Need Assistance; IF, JOIN, DISTINCT, COLLECT.....clueless.

Kris V. Le · 01/20/22

Ok....so please bear with me.

Here is what I am looking to do. I am building a training management system that will essentially comprise 3 different sheets; a training matrix, a training log, and a training status tracker.

The issue I am having is with the status tracker, see below:

I am trying to pull in training tasks into the "Tasks Assigned" from my training matrix sheet based on certain criteria (see below). I will have multiple training modules and a status tracker (above) for each module. I'd like to have a [Tasks Assigned] column formula that Collects and Joins Distinct child training tasks when they are under a specific parent training module. [Tasks Completed] is Collecting and Joining Distinct training tasks that are completed when logged under a specific employee email, which I'm not having any issues with currently using this formula =JOIN(DISTINCT(COLLECT({Employee Trainings}, {Employee Email}, [Employee Email]@row)), CHAR(10)). (If I'm being honest I do not fully understand the "CHAR(10)" functionality to this formula, I copied this formula from a template so any clarity would be helpful).

[Completion Status] reconciles the tasks completed which are retrieved from the log for a specific employee against the tasks assigned and returns the YES or NO value.

I guess the issue I'm having is combing some of the IF and Parent/Child logic to the Join Collect function.

Please help and thanks in advance!

Jeff M. · 01/21/22

The CHAR(10) appears to be a line break between each returned item. This is an option with the JOIN function so that you can add spaces, commas etc.. when joining multiple items. If you select the text wrap option from the top formatting tools, you will see that each item will drop to a new line.

JOIN( range [ delimiter ]) delimiter —[optional] The value to separate the values in the range (e.g., a hyphen, comma, or space). If no delimiter is provided, the values from the range will be joined without spaces (e.g., "Task ATask BTask C")

I'm not 100% clear on what you are attempting to use the IF statement to collect? Could you give an example for what you want to collect & based on what condition?

Kris V. Le · 01/21/22

https://community.smartsheet.com/discussion/comment/316884#Comment_316884

Thanks for the response and info.

the IF I’m trying to collect is I want it to Join and collect all training tasks IF the parent is “Training Module 1” AND the [Job Description 1] is checked.

Jeff M. · 01/22/22

Well I can get you started with this formula used within the same sheet. Maybe someone could help with the cross sheet reference part for pulling it into your training matrix sheet from the tracker sheet.

Maybe you could try making the helper column with the above formula, then referencing the parent cells in the matrix sheet?

=IF([Job Description1]@row = "True", JOIN(DESCENDANTS([Training Task]@row:[Training Task]@row)))

Kris V. Le · 02/11/22

https://community.smartsheet.com/discussion/comment/317046#Comment_317046

So I abandoned the idea of trying to pull in data based on Descendants, because this sheet has multiple parents and descendent rows and it was getting too complicated.

I've added helper columns that I can hide to assist with trying to JOIN data based on two criteria, but by formula is just not working. I'm not getting an error, just a blank field when the second set of criteria are added.

This is the formula I'm using:

=JOIN(COLLECT({Training / Qualification Matrix Range 1}, {Training / Qualification Matrix Range 2}, "1", {Training / Qualification Matrix Range 3}, [Job Title]@row), CHAR(10))

This formula is retrieving data from this sheet"

Jeff M. · 02/12/22

I would suggest troubleshooting your criterion, it's possibly returning false and not collecting data. If the column is a check box, replace "1" in your formula with UPPER(@cell) = true. If you are still having trouble, try adding a new column that contains Yes & No and insert this into the first part, then second part of your formula.

Another method you could try..

=IF(COUNTIFS({Criteria Column Range}, UPPER(@cell) = Criteria (e.g. true, "yes"/"no"), {2nd Criteria Column Range}, [Job Title]@row) > 0, INDEX({Return Data Column from Source Sheet Range}, MATCH([Job Title]@row, {2nd Criteria Column Range}, 0)))

Need Assistance; IF, JOIN, DISTINCT, COLLECT.....clueless.

Answers

Help Article Resources

Categories