Copy Value of Cell from other Rows based on Multiple Criterias

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
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 ....

 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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,"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,315
Office Version
365
Platform
Windows
Cross posted https://www.excelguru.ca/forums/showthread.php?10005-Copy-Value-of-Cell-from-other-Rows-based-on-Multiple-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.
 

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
Thanks Fluff, Sorry About that and Jon .........


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/uploads/2019/05/Excel-Guru.xlsx
 

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
302
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,315
Office Version
365
Platform
Windows
How about
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">31/10/2018</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">06/03/2019</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">11/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">28/10/2018</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">13/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">28/10/2018</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">13/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">13/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">13/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">31/10/2018</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">06/03/2019</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;;">11/03/2019</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: center;border-top: 1px solid black;;">31/10/2018</td><td style="text-align: center;border-top: 1px solid black;;">06/03/2019</td><td style="text-align: center;border-top: 1px solid black;;">11/03/2019</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">[Active Date],MATCH(<font color="Green">1,(<font color="Purple">[Enterprise Yes/No]="Yes"</font>)*(<font color="Purple">[Enterprise ID:]=[@[Enterprise ID:]]</font>)*(<font color="Purple">[Product]="MIL Site"</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">[Active Date],MATCH(<font color="Green">1,(<font color="Purple">[Enterprise Yes/No]="Yes"</font>)*(<font color="Purple">[Enterprise ID:]=[@[Enterprise ID:]]</font>)*(<font color="Purple">[Product]="CRM"</font>),0</font>)</font>),""</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">[Active Date],MATCH(<font color="Green">1,(<font color="Purple">[Enterprise Yes/No]="Yes"</font>)*(<font color="Purple">[Enterprise ID:]=[@[Enterprise ID:]]</font>)*(<font color="Purple">[Product]="IH & Lite CMB"</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
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)​


  • [*=1]MIL Site | ID: 10071E
    [*=1]CRM | ID: 10071E
    [*=1]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.

 

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
I've tried these but no matter what I do It still returns the "" (error).

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,315
Office Version
365
Platform
Windows
Did you confirm the formualae with Ctrl Shift Enter?
 

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
Never Mind I did Get it to work Thanks you!!!!!!

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top