Excel transforming columns based on multiple cells data

I am very new to excel and was trying to transform my data.

I have the following data

And would like to transform it to

Here if any "column" value corresponding to the "ID" is "Yes" in Original dataset the value in the transformed dataset will be "Yes".

With Office 365 Excel we can use:

=LET(id,A2:A9,clm,B2:B9,unq,UNIQUE(id),cnt,IF(COUNTIFS(A:A,unq,B:B,"yes"),"yes","no"),CHOOSE({1,2},unq,cnt))

We are getting a unique list then using that unique list in a COUNTIFS we return if there are any that include at least one Yes. If so we return yes if not no.

The CHOOSE allows us to put both the Unique and the result of the IF in two columns.