Using JOIN / COLLECT with contingent values in multiple columns

Lucas Rayala

I am referencing a sheet with a list of materials and tests performed on the materials. Materials are constantly being added to the list. Each material may need one of 12 tests performed. Once a material is entered in the [Material] column, the sheet populates the 12 possible test columns with "Yes, Perform Test" or "No Test". Alongside each of the test columns is a [date complete] column and a [data review] column. If a material requires Test A, Test C, and Test D, a tech will perform the tests and enter the date the tests were performed. Then a manager will check the data and put an "X" in the data review field. Once all the tests have been performed, a report can be written to release the material.

I need to create a list of materials that need reports written for them. This list will be in a separate sheet. I've been creating similar lists with JOIN/COLLECT functions, but this one has more variables. I would like to COLLECT a list of materials, IF all possible tests have been completed AND data reviews have been checked off, but each material has a different set of tests. I tried a series of IF statements in the range criteria, as I've shown in the below example.

=JOIN(COLLECT({Material}, {TEST_A}, IF(CONTAINS("Yes", @cell), IF({TEST_A_Review} = "x", @cell, "NA"), @cell), {TEST_B}, IF(CONTAINS("Yes", @cell), IF({TEST_B_Review} = "x", @cell, "NA"), @cell))

What I'm trying to get it to do is this:

For a given material, check in the test column (i.e. {TEST_A}). IF that column contains the word "Yes", THEN check the data review column (i.e. {TEST_A_Review}). IF that column contains an "x", then the criteria I want to review the material against is @cell (meaning, the formula accepts this value as true). IF that data review column is not checked, THEN I do not want to add this material to the list. IF the test column does not contain "Yes", THEN I want the formula's review criteria to be @cell as well so that it doesn't reject a material row simply because the test didn't need to be performed.

Anyway, I've tried this a bunch of ways. I could theoretically do a gigantic nested if, but that makes me shudder. Any thoughts?

Paul Newcome

My best suggestion would be what your previous post states. A helper column on the main sheet that indicates if all tests have been completed then pulling based on that.

Leibel S

I would make a helper column for each test that returns true if either the test is not required or the test has been complete.

Then another column for each material that returns true if all the above columns are true.

You collect formula would run on this column.

Lucas Rayala

I ended up making a single helper column that compared the sum of the necessary tests for each material with the sum of the completed reviews. If those two quantities were equal, I pulled the materials into my report.

Lucas Rayala

@Paul Newcome, is it possible to contingently compare two columns in a JOIN/COLLECT if you're referencing another sheet? I can skinny my logic for this issue down to:

COUNTIF(CONTAINS("Yes" , {Test_A}) = COUNTIF(CONTAINS("x", {Test_A_Review})

If these values are equal, regardless of the count, then I know that this material can be selected (if the remaining tests also pass this logic threshold).

Paul Newcome

My best suggestion would be what your previous post states. A helper column on the main sheet that indicates if all tests have been completed then pulling based on that.