Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Copy Value of Cell from other Rows based on Multiple Criterias

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy Value of Cell from other Rows based on Multiple Criterias

    I am having trouble setting some automation. It works best if I explain I have three types of "Products" these products can be purchased Separately or as a collection. The can be "Completed" at different times. I collect the data as separate projects, but need to see them as a collection also ........ With that said Here is what I am trying to do.

    If Collection is a Yes, I want Product A's , from Collection A, Active date Pasted to Cell X

    AND

    If Collection is a Yes, I want Product B's , from Collection A, Active date Pasted to Cell Y

    AND

    If Collection is a Yes, I want Product B's , from Collection A, Active date Pasted to Cell Z

    See Image Below ....


  2. #2
    Board Regular
    Join Date
    Dec 2015
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Guessing from your description Column S is the Company, T is the product, U is Collection, V is collection code, W is active Date. =IF(AND(U2="Yes",T2="Software"),W2,"") This formula in Collection Software Active date will put all things that are yes in collection and software into the the Collection software Active date. Same idea but with hardware for this =IF(AND(U2="Yes",T2="Hardware"),W2,"") . This one will do Billing =IF(AND(U2="Yes",T2="Billing"),W2,"").

    Now if you want to specifcy Company A, here are the three formulas for each in order of software, collection, and then billing

    =IF(AND(U2="Yes",T2="Software",S2="Company A"),W2,"")

    =IF(AND(U2="Yes",T2="Hardware",S2="Company A"),W2,"")

    =IF(AND(U2="Yes",T2="Billing",S2="Company A"),W2,"")

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,838
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Cross posted https://www.excelguru.ca/forums/show...iple-Criterias

    While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
    This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Thanks Fluff, Sorry About that and Jon .........


    Quote Originally Posted by jondavis1987 View Post
    Guessing from your description Column S is the Company, T is the product, U is Collection, V is collection code, W is active Date. =IF(AND(U2="Yes",T2="Software"),W2,"") This formula in Collection Software Active date will put all things that are yes in collection and software into the the Collection software Active date. Same idea but with hardware for this =IF(AND(U2="Yes",T2="Hardware"),W2,"") . This one will do Billing =IF(AND(U2="Yes",T2="Billing"),W2,"").

    Now if you want to specifcy Company A, here are the three formulas for each in order of software, collection, and then billing

    =IF(AND(U2="Yes",T2="Software",S2="Company A"),W2,"")

    =IF(AND(U2="Yes",T2="Hardware",S2="Company A"),W2,"")

    =IF(AND(U2="Yes",T2="Billing",S2="Company A"),W2,"")
    If I am understanding your Formula Correctly it will only evaluate the conditions of that row ....... I would need it to evaluate the conditions of other rows with the matching criteria ..... See Images Below .....






    I've attached a Sample Workbook of it this as well ..... http://phillipbsmith.com/wp-content/...xcel-Guru.xlsx

  5. #5
    Board Regular
    Join Date
    Dec 2015
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    I'm a little bit confused. Looking at your desired results in IHL it looks like you want every cell filled regardless of how many times IH & Lite is a product, Then in CRM you have the same date listed three times with spacing. Mil active date also has more cells filled then you have actual mil products and dates. What is with the seemingly random spacing and repeating of the dates?

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,838
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    How about
    Excel 2013/2016
    HIJ
    231/10/201806/03/201911/03/2019
    328/10/201813/03/2019
    428/10/201813/03/2019
    513/03/2019
    613/03/2019
    731/10/201806/03/201911/03/2019
    831/10/201806/03/201911/03/2019

    Sheet1



    Array Formulas
    CellFormula
    H2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="MIL Site"),0)),"")}
    I2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="CRM"),0)),"")}
    J2{=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="IH & Lite CMB"),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Quote Originally Posted by jondavis1987 View Post
    I'm a little bit confused. Looking at your desired results in IHL it looks like you want every cell filled regardless of how many times IH & Lite is a product, Then in CRM you have the same date listed three times with spacing. Mil active date also has more cells filled then you have actual mil products and dates. What is with the seemingly random spacing and repeating of the dates?

    Because Of Product & Project Information:
    PRODUCT A: (Composed of three different Projects)

    • MIL Site | ID: 10071E
    • CRM | ID: 10071E
    • IH & Lite CMB | ID: 10071E


    PRODUCT B: (Composed of Two different Projects)

    • MIL Site | ID: 10071E.1
    • IH & Lite CMB | ID: 10071E.1

      So I need the all the Associate dates for the other Associate Projects to be listed and if there isn't an associated project then it would be blank.


  8. #8
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    I've tried these but no matter what I do It still returns the "" (error).

    Quote Originally Posted by Fluff View Post
    How about
    Excel 2013/2016
    H I J
    2 31/10/2018 06/03/2019 11/03/2019
    3 28/10/2018 13/03/2019
    4 28/10/2018 13/03/2019
    5 13/03/2019
    6 13/03/2019
    7 31/10/2018 06/03/2019 11/03/2019
    8 31/10/2018 06/03/2019 11/03/2019
    Sheet1

    Array Formulas
    Cell Formula
    H2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="MIL Site"),0)),"")}
    I2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="CRM"),0)),"")}
    J2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="IH & Lite CMB"),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    22,838
    Post Thanks / Like
    Mentioned
    383 Post(s)
    Tagged
    41 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Did you confirm the formualae with Ctrl Shift Enter?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy Value of Cell from other Rows based on Multiple Criterias

    Never Mind I did Get it to work Thanks you!!!!!!

    Quote Originally Posted by Fluff View Post
    How about
    Excel 2013/2016
    H I J
    2 31/10/2018 06/03/2019 11/03/2019
    3 28/10/2018 13/03/2019
    4 28/10/2018 13/03/2019
    5 13/03/2019
    6 13/03/2019
    7 31/10/2018 06/03/2019 11/03/2019
    8 31/10/2018 06/03/2019 11/03/2019
    Sheet1

    Array Formulas
    Cell Formula
    H2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="MIL Site"),0)),"")}
    I2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="CRM"),0)),"")}
    J2 {=IFERROR(INDEX([Active Date],MATCH(1,([Enterprise Yes/No]="Yes")*([Enterprise ID:]=[@[Enterprise ID:]])*([Product]="IH & Lite CMB"),0)),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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