troubleshooting Question

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

Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland asked on
VBAMicrosoft ExcelMicrosoft Office
1 Comment1 Solution84 ViewsLast Modified:
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

Thanks
j
QC-Check-Calc_v2.xlsm
ASKER CERTIFIED SOLUTION
Jase Alexander
Compliance Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros