Using Countif to return un-duplicated instances

In one column I have unique numerical IDs which could be duplicated up to three times. In another column I have the inputs "Yes" and "No" which correspond randomly to these IDs.

In a third column I would like to return an affirmative output for the first instance of "Yes" for each unique ID code.

For instance:

Column A

23
23
23
4
4
6

Column B

No
No
Yes
Yes
Yes
No

Desired Column C

0
0
1
1
0
0

Assuming data starts at row 2 try this formula in C2 copied down

=(COUNTIFS(A$2:A2,A2,B$2:B2,"Yes")=1)+0

I believe that formula returns the required results for your example. Can you give me an example where it doesn't work?

Yes, if you put that in C2 and copy down, given that your other data starts at row 2 you should get the output that you requested, with 1 for an affirmative. If you want you could use just

=COUNTIFS(A$2:A2,A2,B$2:B2,"Yes")=1

for TRUE/FALSE results

Sorry, I didn't consider that possibility so the formula I suggested would be incorrect in some circumstances......

This will fix it.....

=AND(COUNTIFS(A$2:A2,A2,B$2:B2,"Yes")=1,B2="Yes")

=--IF(B2="no",FALSE,COUNTIFS($A$2:A2,A2,$B$2:B2,"yes")=1)
hmm i see above works fine!