# Excel: Setting Column Values Based on Unique ID

1. ## 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?

2. ## Re: Excel: Setting Column Values Based on Unique ID

maybe so
Formula:
`Please Login or Register  to view this content.`

3. ## Re: Excel: Setting Column Values Based on Unique ID

No need for any code, try this

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

4. ## Re: Excel: Setting Column Values Based on Unique ID

Try this as the Flag formula.

=IF(COUNTIFS(A:A,A2,B:B,1)>=1,"Yes", "No")

5. ## Re: Excel: Setting Column Values Based on Unique ID

Thanks, chaps. Both solutions worked perfectly!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1