+ Reply to Thread
Results 1 to 9 of 9

Populating Status and Date from Other Cell Based on ID

  1. 07-13-2018, 10:31 PM #1
    TeamOSupremeO
    TeamOSupremeO is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Populating Status and Date from Other Cell Based on ID

    I have lines of data that contain unique identifiers. Each line represents a step in the process that has occurred.

    I would like to populate two fields based on data that is returned from the same unique ID.

    Please see the attached example.

    In Column E, the formula should return "Complete" if the matching unique ID has any step listed as "Complete". Column E should return "Complete" for all unique IDs except for ID CCCC because it has no "Complete" steps. IDs CCCC should return as "In-Process"

    In Column F, the date which produced the "Complete" step status (Column C) should be populated for the unique id represented. If multiple dates are complete steps for a unique ID, the later of the two dates should be used. All AAAA items should have the Item Complete Date of 7/13/2018. The return for CCCC should be "In Process", since it has no complete steps in the dataset.
    Attached Files Attached Files
    Last edited by TeamOSupremeO; 07-17-2018 at 04:38 PM. Reason: Solved
    Reply With QuoteRegister To Reply
  2. 07-13-2018, 11:12 PM #2
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Populating Status and Date from Other Cell Based on ID

    Hi,

    For Column E, in E2, use:

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


    Formula copied down.

    For Column F, in F2, use:

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


    Formula copied down. NOTE: this is an Array formula, requires CSE (Control, Shift, Enter)

    See that you are using 365, so you should have the MAXIF or MAXIFS function which can be used for Column F; however, I don't have 365, so I can not provide a formula using that function.

    Refer to attached sample.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 07-13-2018, 11:18 PM #3
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,452

    Re: Populating Status and Date from Other Cell Based on ID

    Try in E2:
    Please Login or Register  to view this content.
    And in F2:
    Please Login or Register  to view this content.
    ...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 your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Drag both down
    Quang PT
    Reply With QuoteRegister To Reply
  4. 07-16-2018, 05:50 PM #4
    TeamOSupremeO
    TeamOSupremeO is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Populating Status and Date from Other Cell Based on ID

    The above formulas work, however, I would like to adjust the formula in F2 to show the max date that a step reached complete status. For example, if AAAA has an in-process step after the complete date of 7/13/18, I would still like it to return 7/13/18 for the complete date.
    Reply With QuoteRegister To Reply
  5. 07-16-2018, 05:59 PM #5
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Populating Status and Date from Other Cell Based on ID

    Quote Originally Posted by TeamOSupremeO View Post
    The above formulas work, however, I would like to adjust the formula in F2 to show the max date that a step reached complete status. For example, if AAAA has an in-process step after the complete date of 7/13/18, I would still like it to return 7/13/18 for the complete date.
    Please clarify who and/or which formula you're referring to.
    Reply With QuoteRegister To Reply
  6. 07-16-2018, 06:14 PM #6
    TeamOSupremeO
    TeamOSupremeO is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Populating Status and Date from Other Cell Based on ID

    Thank you for your reply, jtakw. I was referring to the formula in F2 and copied down that both you and bebo provided. Both formulas work, however I found an instance in my real data where an in-process step was submitted after a complete step. Id like to calculate off of the max complete step date. File attached for reference.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-16-2018, 07:52 PM #7
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Populating Status and Date from Other Cell Based on ID

    Ok,

    I disagree that the F2 formula provided by bebo in Post #3 would work correctly, because bebo's formula for E2 prevents E2 to ever be "Blank", and yet the F2 formula tests for a "" (Blank) for E2 - Probably a typo or oversight.

    Use this updated formula for your added requirement in F2:

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


    Array formula confirmed by CSE.

    See attached sample, I've included my formulas as well as Bebo's to compare results.
    Attached Files Attached Files
    Last edited by jtakw; 07-16-2018 at 09:39 PM.
    Reply With QuoteRegister To Reply
  8. 07-17-2018, 11:21 AM #8
    TeamOSupremeO
    TeamOSupremeO is offline
    Registered User
    Join Date
    07-13-2018
    Location
    Columbus, OH
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Populating Status and Date from Other Cell Based on ID

    Thanks, jtakw. I stand corrected, your formula worked perfectly.

    Appreciate your assistance on this. Thank you!
    Reply With QuoteRegister To Reply
  9. 07-17-2018, 06:27 PM #9
    jtakw
    jtakw is offline
    Valued Forum Contributor jtakw's Avatar
    Join Date
    05-05-2018
    Location
    CA, USA
    MS-Off Ver
    2016
    Posts
    668

    Re: Populating Status and Date from Other Cell Based on ID

    You're welcome, glad they worked for you.

    Add Rep would be appreciated, click the Star at the lower left corner of the Posts you found helpful.
    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. VBA to Update status Column based on recent date
    By edkawy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-17-2017, 10:06 AM
  2. Need help with populating a cell with a number, based on an approximate date match
    By Stigdu in forum Excel General
    Replies: 8
    Last Post: 01-22-2016, 07:54 AM
  3. [SOLVED] Duplicate thread, pls ignore
    By Stigdu in forum Excel General
    Replies: 0
    Last Post: 01-22-2016, 05:54 AM
  4. Graphical indicator based on due date and status field
    By lysacush in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-03-2014, 02:19 PM
  5. Conditional Formatting based on end date and status
    By bunchomunkies in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2013, 02:10 PM
  6. Help: IF Nested condition based on Date to determine Status
    By shoro in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 01-10-2012, 10:21 AM
  7. Populating a cell with a calendar month based on the previous date in another cell.
    By avidcat in forum Excel General
    Replies: 3
    Last Post: 01-09-2010, 08:42 AM

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