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
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.