Find same value in column 1, if all values are X in column 2, give Y in column 3-CodePudding

My goal is to get 1 single formula in Column 3 which does the following:

Search for all exact same values as themself in Column 1. If all the values are YES in Column 2, give YES in Column 3. If not all values are YES in Column 2, give NO in Column 3.

My actual dataset is 10k rows, with "id's" in Column 1, so this is a simplified example.

I know it is not the prettiest question, but thanks in advance for all the help :).

Kind regards, Jurgen

CodePudding user response：

Assumptions:

Row 1 is a header row and actual data starts in row 2
Column 1 is A
Column 2 is B

Use this formula in cell C2 (or row 2 of whatever Column 3 actually is) and copy down:

=IF(COUNTIF(A:A,A2)=COUNTIFS(A:A,A2,B:B,"YES"),"YES","NO")

Adjust formula column references as necessary to suit your data. If it runs too slowly, limit the column references to your actual dataset instead of using full column.