We help IT Professionals succeed at work.
Get Started

Excel Formula / VBA Caluclate No of Hours based on attribute criteria as one array or VBA is possible

Last Modified: 2020-01-22
Hi Experts

I hope you can help

I have an Excel sheet that generates data from our consignment values (Columns A to I – this is pasted in). This data ultimately breaks down, per hour, how may items from the shipment needs to be quality checked based on their attributes (Maternity Clothing, Kids clothing or Product)

Column K performs a lookup to the product file that assigns the attribute to the shipment based on the biggest percentage of product mix – this is abbreviated to PRO, MAT or KID) – the result of this is extracted to cell N4

Cell M15 downward extracts the unique purchase order references from the shipment and column N sums the quantity of items per PO.

To the right, in cells V9 to W18 contains the criteria on which the number of hours is calculated against the quantity on each PO

Finally, cells V2 to W4 contain the items per hour that our QC have informed us they are able to check

For the purpose of this request, I need a result in Column O (beginning in O15 against each PO line), that, based on the attribute in N4, picks the correct criteria and calculates the number of hours that is required so our resource planner can be updated

For example, in the uploaded file, I have included the desired result against the first PO using a nested formula on the assumption that the attribute is KID (it does not use N4 as a trigger just the criteria only), however, what I need is a formula or VBA that can encapsulate this result based on the attribute. Therefore, it should say if N4=KID and if the PO qty if less than or equal to 100, then divide 5 (in cell W16 - qty needed to check) by 105 in cell W2 (number of items that can be checked per hour) and so on and then if N4-PRO then use the criteria from V10 to W13 etc.

I don’t know how to assemble all this information into one formula. I’ve tried an array but could get this to work successfully but would ultimately want to avoid having separate columns, one for each attribute with the corresponding nested formula.

If you can help in any way I would be very grateful

Watch Question
Compliance Manager
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE