Forum Discussion
Filter certain project for which everything is finished
Hello everyone,
I am searching for a way to do the following:
I have a table which looks like this:
| project | part | check 1 | check 2 |
| Project A | 1 | OK | NOK |
| Project A | 2 | OK | OK |
| Project A | 3 | OK | OK |
| Project B | 1 | OK | OK |
| Project B | 2 | OK | OK |
I want to show a table (on another sheet) which has the following result:
| project |
| Project B |
Because 'project B' is the only project with all checks on 'OK'
This sounds very easy, but i can't seem to find a solution.
Thank you all for your time,
Dieter
=LET(e,UNIQUE(A2:A6),ok,COUNTIFS($A$2:$A$6,e,$C$2:$C$6,"OK",$D$2:$D$6,"OK"),project,COUNTIF($A$2:$A$6,e),result,FILTER(e,ok=project),result)Maybe with this formula which seems to work in my sheet.
9 Replies
I can't help feeling this looks somewhat complicated?
= LET( NOKλ, LAMBDA(aProject, OR(FILTER(checks,project=aProject)="NOK")), distinctProjects, UNIQUE(project), incomplete, MAP(distinctProjects,NOKλ), FILTER(distinctProjects, NOT(incomplete), "No complete projects") )The first Lambda function is defined to accept a single project, filters the checks and looks for "NOK".
UNIQUE returns a list of distinct projects
MAP runs through each distinct project and returns TRUE if it is incomplete (using NOKλ).
FILTER selects projects that of not incomplete (i.e. completed)
OliverScheurich Does your Excel read the file despite language differences?
The OR and NOT formulas are read and translated but the UNIQUE formula isn't read and translated.
However the whole LET formula can't be executed because i don't work with Office365.
- So that confirms that it is worth you posting Excel files rather than pdfs, since they will be translated by the receiving Excel version. Shame about the 365 issue though, 'ODER und NICHT' look a bit out of place within a sea of errors! Early on, in 2019, I gave up on traditional spreadsheets and moved entirely to Dynamic Array solutions that suited me far better. Prior to that, I had schooled myself to commit every formula with CSE but it was painful as a working environment.
- Please try the formula below.
=IFNA(UNIQUE(FILTER(A2:A6,A2:A6<>FILTER(A2:A6,(C2:C6="nok")+(D2:D6="nok"),""))),"") - hi quadraple,
could you please let me understand filter part in above formula? =LET(e,UNIQUE(A2:A6),ok,COUNTIFS($A$2:$A$6,e,$C$2:$C$6,"OK",$D$2:$D$6,"OK"),project,COUNTIF($A$2:$A$6,e),result,FILTER(e,ok=project),result)Maybe with this formula which seems to work in my sheet.
- Hey,
Thank you for your response, could you link me the original file, it doesn't seem to work for me.
It says the first COUNTIFS function has to many arguments ...
Thank you very much,
Best Regards,
Dieter WatelleI can send you the .pdf with the original file however it's in german. I can use formulas such as FILTER, UNIQUE and LET in Excel online but to my knowledge i can't attach these files to my post.
