Excel: Setting Column Values Based on Unique ID

Hi,

I have a spreadsheet of data containing a unique ID and a binary field, where each unique ID will have multiple rows. What I needed to do (programmatically or otherwise) is to create a new field (called 'Flag') that marks as 'Yes' or 'No' a set of client rows that have at least one value of '1' in the binary field. But I can't figure out a formula that works!

See the table below for an idea of what I mean:


ClientID BinaryField Flag
1 0 Yes
1 0 Yes
1 0 Yes
1 1 Yes
1 0 Yes
1 0 Yes
2 1 Yes
2 0 Yes
2 1 Yes
2 0 Yes
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
3 0 No
4 1 Yes
5 0 No
5 0 No
5 0 No
5 0 No
5 0 No


Where there is a 'Yes' value in column C, there should be at least one value of '1' in column B. A 'No' should only be assigned when a set of client rows has a 0 value for all its rows.


Any ideas anyone?

Thanks in advance!!

maybe so

Formula:

Please Login or Register  to view this content.

No need for any code, try this

=IF(SUMIF($A$2:$A$28,A2,$B$2:$B$28)=0,"No","Yes")

Thanks, chaps. Both solutions worked perfectly!