Macro to automate our reporting task

Hi Guys,

I need help to speed up our reporting task and I'm trying to solve this through VBA solution. 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 attached file for reference.

Conditions:

1. If PO's have same reference number and doc. type or there's only a single PO and the document 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 are the same but have different document type and the cell on Vendor1 column is blank, below is the condition:
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 are the same but 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.

Thank you in advance and it will be greatly appreciated any assistance that you'll provide. More power to Excel Forum!

Hi geh,

As I start putting in formulas in your Status column and get to step 2 I was wondering... Is the whole problem done if we simply put your required words in the Status Column? See what I have so far.

Needs a VBA Solution.xlsx

Hi geh,

My goal is to have you help write this formula so you can "fix" it later when your requirements change. Have you seen what I've done so far? Can you tell me how it works? Do you think you might add the next step?

Can you show me the answer to your question in an attached file? I'm confused by your last few steps.