How do you keep passing projects from a numeric 0/1 checklist?
Use numeric 1 for passed and 0 for failed or untested checks. Keep projects whose minimum for both checks is 1. Every required part and check value must be present.
Start with a checklist that records passed checks as numeric 1 and failed or untested checks as numeric 0. In JOIN SHEET, find the minimum for each check per project, then keep projects where both minimums are 1. The input must include every required part and every check value. Filtering to passing rows before the summary would hide failed parts.
Record unfinished checks as zero
The source question uses OK/NOK labels. For this fictional input, prepare numeric 1 for passed and numeric 0 for failed or not yet checked before importing. JOIN SHEET does not automatically make that conversion. Confirm that every check value is numeric 0 or 1 and that no required part is missing. Minimum calculations can ignore blanks, so an untested check must not be left blank.
| Project | Part | Check 1 | Check 2 |
|---|---|---|---|
| P1 | A | 1 | 1 |
| P1 | B | 1 | 0 |
| P2 | C | 1 | 1 |
| P2 | D | 1 | 1 |
| P3 | E | 0 | 1 |
Take minimums without removing failed rows
- Select Project checks as the result source and add Summarize in Refine results. Do not place a passing-row filter before it.
- Select Project under Group by. Change the existing Summary method from Count to Minimum, select Check 1, and enter Minimum check 1 in Output field name.
- Use Add summary in the same step for Check 2: choose Minimum and enter Minimum check 2. Use Add summary once more, keep Count, and name it Part count.
- Below the summary, add a Filter for Minimum check 1 with Equals and value 1, then another for Minimum check 2 with Equals and value 1.
- Choose Apply. Confirm that only P2 remains, with Part count equal to 2.
Why P1's passing row is not enough
P1's part B has a zero in Check 2, making the project's minimum zero. Both checks on both of P2's parts are one, so P2 qualifies. P3 fails the first-check condition. The test concerns every loaded part, not the existence of a single passing row.
| Project | Minimum check 1 | Minimum check 2 | Part count |
|---|---|---|---|
| P2 | 1 | 1 | 2 |
A required part missing from the file cannot appear in this check. Compare the input with your expected-parts list first. Keep untested parts in the table with zero for their checks; removing them or leaving checks blank could make an unfinished project appear complete.
Try it with your own files
Start with JOIN SHEET ↗Use a desktop computer for Excel and CSV work.
Opens in a new tab without replacing your current work.
Example last verified:
Sources
The question Filter certain project for which everything is finished asks how to keep projects only when all their work is finished. The checks used here are fictional.
