I have a list with similar content to this:
<colgroup><col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:3669;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:1820;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:2702;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2929;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:1820;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:3100;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:1393;width:29pt" width="39"> <col style="mso-width-source:userset;mso-width-alt:2019;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2730;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:4124;width:87pt" width="116"> </colgroup><tbody>
</tbody>
it is located in columns B to L, and the data (after header/titles) starts in row 104 and goes to 1000.
I have another chart that looks like this:
<tbody>
</tbody>
it is located in columns B to D and the data (after headers/titles) starts in row 56.
I want the second chart to be filled in with information from the first under the following conditions:
-if the last column is "Acquisition" and Net New Money $ is >100,000 then fill in chart 2 with the following information from chart 1: the Client Name, $ (Net New Money $) and Part. %
-if the last column is "Repeat" nothing should be done
-if the last column is "Referral" then fill in Chart 2 column "Client Name" from Chart 2 and then put 0 under the "$" column and 100 under "Part. %"
I know how to use if statements to get this info on one line but, I am not sure how to make a full list in Chart 2 from only certain information in Chart 1. Any help you can provide would be appreciated.
Thank you
Abacus No. | Client Name | CRM ID | Auth. Date | Accept. Date | Net New Money | Part. | After Sales Split | Acquisition/Repeat | ||
# | $ | % | # | $ | ||||||
01234567 | ABC Co | 123456 | 29/03/2019 | 02/04/2019 | 1 | 150000 | 100 | 1 | 150000 | Acquisition |
02345678 | DEF Co | 234567 | 24/04/2019 | 25/04/2019 | 1 | 145000 | 100 | 1 | 145000 | Repeat |
03456789 | GHI Co | 345678 | 29/04/2019 | 02/05/2019 | 1 | 895000 | 50 | 0.5 | 447500 | Referral |
<colgroup><col style="mso-width-source:userset;mso-width-alt:2616;width:55pt" width="74"> <col style="mso-width-source:userset;mso-width-alt:3669;width:77pt" width="103"> <col style="mso-width-source:userset;mso-width-alt:1820;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:2702;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:2929;width:62pt" width="82"> <col style="mso-width-source:userset;mso-width-alt:1820;width:38pt" width="51"> <col style="mso-width-source:userset;mso-width-alt:3100;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:1393;width:29pt" width="39"> <col style="mso-width-source:userset;mso-width-alt:2019;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:2730;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:4124;width:87pt" width="116"> </colgroup><tbody>
</tbody>
it is located in columns B to L, and the data (after header/titles) starts in row 104 and goes to 1000.
I have another chart that looks like this:
Client Name | $ | Part. % |
<tbody>
</tbody>
it is located in columns B to D and the data (after headers/titles) starts in row 56.
I want the second chart to be filled in with information from the first under the following conditions:
-if the last column is "Acquisition" and Net New Money $ is >100,000 then fill in chart 2 with the following information from chart 1: the Client Name, $ (Net New Money $) and Part. %
-if the last column is "Repeat" nothing should be done
-if the last column is "Referral" then fill in Chart 2 column "Client Name" from Chart 2 and then put 0 under the "$" column and 100 under "Part. %"
I know how to use if statements to get this info on one line but, I am not sure how to make a full list in Chart 2 from only certain information in Chart 1. Any help you can provide would be appreciated.
Thank you