Forum Discussion
Solved
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 Pro...
=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.
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.