Avatar of Jase Alexander
Jase AlexanderFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

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

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

VBAMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Jase Alexander
Avatar of Jase Alexander
Jase Alexander
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo