+ Reply to Thread
Results 1 to 5 of 5

Excel: Setting Column Values Based on Unique ID

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    30

    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!!

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,370

    Re: Excel: Setting Column Values Based on Unique ID

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

  3. #3
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607

    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. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    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")
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  5. #5
    Registered User
    Join Date
    12-10-2014
    Location
    UK
    MS-Off Ver
    2010
    Posts
    30

    Re: Excel: Setting Column Values Based on Unique ID

    Thanks, chaps. Both solutions worked perfectly!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  2. Get unique values from column and get Counts and Sums based on these values
    By khhleung in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2014, 09:58 PM
  3. Count Unique values in Column based on values in other columns
    By dmschave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-17-2014, 11:06 AM
  4. Return number of unique values based on values in other column
    By Medir in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-26-2013, 11:17 AM
  5. Replies: 1
    Last Post: 11-16-2013, 05:18 AM
  6. [SOLVED] Filter rows based on unique values in one column + specific value in another column
    By Dieneces in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2013, 09:55 AM
  7. [SOLVED] Filtering and generating new tabs based on Unique values based in column
    By amlan009 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-02-2012, 09:40 PM

Bookmarks

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