+ Reply to Thread
Results 1 to 9 of 9

Please help a newbie to populate one column based on data in another column. Thanks.

Hybrid View

  1. 08-05-2016, 06:05 AM #1
    Zdog
    Zdog is offline
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Lightbulb Please help a newbie to populate one column based on data in another column. Thanks.

    Hi Everybody.

    I am new here, so please bear with me whilst I get a handle on how things work around here. I have just started a job which will involve considerable Excel usage, and I have already been set a task I can’t solve. Please could someone advise? I suspect it is very simple for all you gurus.

    Attached is an example spreadsheet to illustrate the problem. Basically, I want to fill column D with either ‘Yes’ or ‘No’ based on data in column C, for each country (column A). If there is a single ‘yes’ in column C, I want to populate all of column D with ‘yes’, per country. If there is no ‘yes’ in column C, then I want to populate all of column D with ‘no’, per country.

    Does that make sense? Basically, if a country has a region (Column B) that is on the watch list, then I want to put that Country on the Watch list (column D).

    Penny for you thoughts.

    Regards,
    Gareth
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-05-2016, 06:15 AM #2
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Try this in D2 and drag down
    Formula: copy to clipboard 
    =IF(COUNTIFS($C$2:$C$17,"Yes",$A$2:$A$17,$A2)>0,"Yes","No")
    Reply With QuoteRegister To Reply
  3. 08-05-2016, 06:37 AM #3
    Zdog
    Zdog is offline
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Thanks for the help and rapid response. However, the sample data I uploaded was a very small version of much bigger file. In the actual file there are many more countries (not just A and B), so "$C$2:$C$17" cant be used. Is there some way I can replace this to accommodate this? Thank you.

    I have attached a slightly larger version of the file, plus both Jose and Glenn's equations.
    Attached Files Attached Files
    Last edited by Zdog; 08-05-2016 at 06:51 AM. Reason: forgot attachment
    Reply With QuoteRegister To Reply
  4. 08-05-2016, 06:16 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Try this array formula:

    =IF(ISNUMBER(MATCH(A2&"Yes",$A$2:$A$17&$C$2:$C$17,0)),"Yes","No")

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  5. 08-05-2016, 06:16 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Jose's answer is simpler. if it is OK for you - use it instead!
    Reply With QuoteRegister To Reply
  6. 08-05-2016, 06:39 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,324

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Before anybody goes any further with this, please provide a sample workbook that includes all the relevant layouts and data types. It will save everybody time in the long run. Make sure it includes all possible scenarios.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  7. 08-05-2016, 06:40 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Just extend the range of the formulae to cover your range. Indeed, if you're using Jose's answer, simplify it to:

    =IF(COUNTIFS(C:C,"Yes",A:A,$A2)>0,"Yes","No")
    Reply With QuoteRegister To Reply
  8. 08-05-2016, 06:59 AM #8
    Zdog
    Zdog is offline
    Registered User
    Join Date
    08-05-2016
    Location
    Manchester
    MS-Off Ver
    2010
    Posts
    18

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    Awesome - thank you, both of you. This resolves the issue. Your rapid and helpful responses have been much appreciated.

    Peace and Tranquility,
    G
    Reply With QuoteRegister To Reply
  9. 08-05-2016, 07:00 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,324

    Re: Please help a newbie to populate one column based on data in another column. Thanks.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate one sheet with data from another based on matching column value
    By thepinkgeologist in forum Excel General
    Replies: 1
    Last Post: 09-09-2015, 10:36 AM
  2. Populate one sheet with data from another based on matching column value
    By thepinkgeologist in forum Excel General
    Replies: 5
    Last Post: 09-09-2015, 09:49 AM
  3. [SOLVED] populate column data based on matching from other sheet
    By sarat47.dash in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-17-2015, 10:36 AM
  4. [SOLVED] Populate a Column Based on the Change in data of Another Column
    By bridgeport in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-24-2014, 11:37 AM
  5. [SOLVED] Auto populate a column based on a drop down choice from another column ?
    By JBenn911 in forum Excel General
    Replies: 5
    Last Post: 01-29-2014, 01:35 PM
  6. Populate one column with data based on another column.
    By Ronnie_Greytruck in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-04-2013, 02:12 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1