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.

You've received three accurate answers which makes it a bit tough to identify just one that is "good". But please pay forward by accepting one of the these. — Tedinoz
– Tedinoz, Commented Nov 28, 2024 at 1:17

Harun24hr · Accepted Answer · 2024-11-25 05:45:12Z

1

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)))))}

answered Nov 25, 2024 at 5:45

Harun24hr

38k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Harun24hr Nov 25, 2024 at 6:48

You may also try little bit simpler one,

={UNIQUE(TOCOL(A1:A,1)),REDUCE(TOCOL(,1),UNIQUE(TOCOL(A1:A,1)),LAMBDA(a,x,VSTACK(a,IF(COUNTIFS(A:A,x,B:B,"Fail")>0,"Fail","Pass"))))}

.

Venara · Accepted Answer · 2024-11-28 03:53:40Z

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

edited Nov 28, 2024 at 3:53

answered Nov 25, 2024 at 4:06

Venara

8332 gold badges4 silver badges14 bronze badges

2 Comments

Venara Nov 26, 2024 at 6:32

Welcome to Stack Overflow! If this answers your question, please click the accept button on the left (check icon) and upvote the answer. By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. If the accept button is unavailable to you, feel free to tell me. how to accept the answer

Tedinoz Nov 28, 2024 at 1:07

A very small issue, but the OP spells "Pass" and Fail" with leading Caps. There is something to be said for using lowercase to "detect" the Column B value, but then use leading caps for the answer.

rockinfreakshow · Accepted Answer · 2024-11-25 07:09:01Z

0

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) ''")

answered Nov 25, 2024 at 7:09

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

2 Comments

Andy Nov 26, 2024 at 3:20

Thanks a bunch to James Z (for cleaning my post), Harun24hr, Alma_Matters, and rockinfreakshow ! All solutions work great! Much appreciated.

Tedinoz Nov 28, 2024 at 1:15

??? Why would this answer be down-voted; of all the answers (and they are all good) it is the least complicated, easiest to understand.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related