How do I get the unique item (remove duplicates) from column A and display pass if all values are pass from column B, otherwise it's a fail

Question

How do I output the unique item (remove duplicates) from column A and output pass if all values are pass and fail if even one value is a fail or blank from column B:

Example:

Column A   Column B
jack             Pass
jack             Pass
jack             Pass
jack             Pass
don              Pass
don              Fail

So, column C and D should be:

Column C   Column D  
jack             Pass
don              Fail

I tried this to remove duplicates =UNIQUE(A1:A6) not sure how to combine another formula to display column C and D.

Answer 1

As I understand your question, these are the requirements you want to achieve:

Pass: The result is "Pass" only if all corresponding values in Column B are "Pass".

Fail: The result is "Fail" if there is at least one "Fail" or if any corresponding value in Column B is blank.

You can try this formula:

=ARRAYFORMULA(ARRAY_CONSTRAIN({UNIQUE(FILTER(A1:A, A1:A<>"")), IF(COUNTIF(FILTER(A1:A, (B1:B="Fail")+(B1:B="")), UNIQUE(FILTER(A1:A, A1:A<>""))) > 0, "Fail", "Pass")}, COUNTA(UNIQUE(FILTER(A1:A, A1:A<>""))), 2))

Sample Output:

Reference:

CountIf

Array_Constrain

Filter

Answer 2

Here's one approach you may test out:

=query({A:A,switch(B:B,,"Fail",B:B)},"Select Col1,min(Col2) Where Col1!='' group by Col1 label min(Col2) ''")

Answer 3

you may try

={UNIQUE(TOCOL(A1:A,1)),REDUCE(TOCOL(,1),UNIQUE(TOCOL(A1:A,1)),LAMBDA(a,x,VSTACK(a,LET(t,UNIQUE(FILTER(B:B,A:A=x)),IF(COUNTA(t)>1,"Fail",t)))))}