Hi Guys,
I need help to speed up our reporting task and I hope if this can be solve with formula or through VBA solution, if possible. Below are the conditions for the project and I'm really struggling on how I can make this work and integrate all of these in one solution. Please see the file on below link for reference.
Dropbox - Book1.xlsx
Conditions:
1. If PO's have same reference number and same doc. type and the doc. type is KP, the status is always "for PO Closure". If doc. type is ZM/RE,
status is "Pending with GR". If doc. type is WE status is "Pending with IR". If doc. type is WL, status is "With returned GR".
2. If PO's have the same reference no. but one or more PO's in the group have different document type and the cell on Vendor1 column is blank, below are the conditions:
a. Sum the amount of PO with same reference number and document type. If ZM/RE is more than WE in the PO group, status is "IR>GR". If WE is more than ZM/RE in
the PO group, status is "GR>IR".
3. If the PO's have the same reference no. but one or more PO's in the group have different document type and the cell on Vendor1 column is not blank, below are the conditions:
a. Sum the total amount of PO with same reference number and regardless of document type. If total amount is less than 2,500 USD and cell on column Del
completed is ticked as yes, status is "for PO closure".
b. Sum the total amount of PO with same reference number and regardless of document type. If total amount is more than 2,500 USD and cell on column Del
completed is ticked as yes, status is "verify if for PO closure"
4. VBA/formula should be variable to accommodate any number of rows.
As a sample given on the file provided on the link, PO reference number 3500152140 consists of 10 doc. type RE and 1 doc. type WE, step 2 & 3 will apply.
Thank you in advance and it will be greatly appreciated any assistance that you'll provide. More power to Mr. Excel!
I need help to speed up our reporting task and I hope if this can be solve with formula or through VBA solution, if possible. Below are the conditions for the project and I'm really struggling on how I can make this work and integrate all of these in one solution. Please see the file on below link for reference.
Dropbox - Book1.xlsx
Conditions:
1. If PO's have same reference number and same doc. type and the doc. type is KP, the status is always "for PO Closure". If doc. type is ZM/RE,
status is "Pending with GR". If doc. type is WE status is "Pending with IR". If doc. type is WL, status is "With returned GR".
2. If PO's have the same reference no. but one or more PO's in the group have different document type and the cell on Vendor1 column is blank, below are the conditions:
a. Sum the amount of PO with same reference number and document type. If ZM/RE is more than WE in the PO group, status is "IR>GR". If WE is more than ZM/RE in
the PO group, status is "GR>IR".
3. If the PO's have the same reference no. but one or more PO's in the group have different document type and the cell on Vendor1 column is not blank, below are the conditions:
a. Sum the total amount of PO with same reference number and regardless of document type. If total amount is less than 2,500 USD and cell on column Del
completed is ticked as yes, status is "for PO closure".
b. Sum the total amount of PO with same reference number and regardless of document type. If total amount is more than 2,500 USD and cell on column Del
completed is ticked as yes, status is "verify if for PO closure"
4. VBA/formula should be variable to accommodate any number of rows.
As a sample given on the file provided on the link, PO reference number 3500152140 consists of 10 doc. type RE and 1 doc. type WE, step 2 & 3 will apply.
Thank you in advance and it will be greatly appreciated any assistance that you'll provide. More power to Mr. Excel!
