+ Reply to Thread
Results 1 to 9 of 9

Populating Status and Date from Other Cell Based on ID

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

  2. #2
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    581

    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

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,746

    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

  4. #4
    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.

  5. #5
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    581

    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.

  6. #6
    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

  7. #7
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    581

    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.

  8. #8
    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!

  9. #9
    Valued Forum Contributor
    Join Date
    05-05-2018
    Location
    Oakland, CA
    MS-Off Ver
    2016
    Posts
    581

    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 to Thread

Thread Information

Users Browsing this Thread

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

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