Oct 15 2023 10:27 AM
Oct 15 2023 10:27 AM
Good day. I am trying to do some calculations on a matrix of products to assemble (column A) and the components they are comprised of (row 1). Some of these components are used on multiple products. What I would like to do is have 2 lists, 1 of the products to assemble during the day and the other a list of all components needed to accomplish the tasks.
I have the matrix completed and filled out with the number of each component required for assembly. On another sheet I created a list of the products (column A) and Qty to build (column B) as well as a list of components (column G) and Qty needed for builds (column H).
I thought that it might be possible to use an IF statement to make sure column B was > 0, then do a lookup based off column A of both sheets and have it then match the component name in column g with row 1 of the matrix sheet. Then it would need to sum all the components for all the products and possibly multiply them.
I was working on just getting the lookup portion working and am probably in over my head and could use help. I have attached screenshots of a sample of what I'm working on, as I'm sure I didn't explain it very well.
Thank you to anyone who might have better methods or can help in any way.
Oct 15 2023 10:58 AM
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Oct 15 2023 12:18 PM
Oct 15 2023 12:25 PM - edited Oct 15 2023 12:35 PMSolution
The mathematical approach for this problem is to use MMULT for the matrix multiplication
= MMULT(TRANSPOSE(compsPerProduct), productQuantity)
Sadly the formula is unlikely to appeal to those without a STEM background. There is an alternative using 365 array formulas.
= LET( componentsNeeded, productQuantity * compsPerProduct, neededOverall, BYCOL(componentsNeeded, LAMBDA(x, SUM(x))), TOCOL(neededOverall) )
The first line simply multiplies the component table by the number of products required to determine the component count for each product. Next, summing by column gives the overall component counts. The result is a row of counts so the final step converts it to a column. Fortunately the two methods give identical results.
Oct 15 2023 11:03 PM
First and foremost, thank you. It appears that the MMULT with the TRANSPOSE should fit perfectly for what is needed.
Secondly, thank you for showing me that you can define ranges of data to make it easier to call up when working with formulas, I never even realized that was possible.
I attempted to recreate your sample within my example linked from my OneDrive, but am just getting the #VALUE! error, so not sure exactly what I'm doing differently.
I'll check into it a little more tomorrow and see if I can figure out what's going on to get it working on my workbook.
Oct 16 2023 01:11 AM
@kurtlee MMULT is a very picky function in that it does not fail gracefully. If the number of product rows/columns in the two tables do not match the function will crash and burn rather than reporting errors in the final elements to be returned.
Another catch is that it does not accept blank cells as zeros; the 0 must be entered explicitly. Otherwise you would need a formula step to test each array, element by element, and return 0 if the test fails (the element value otherwise).
= MMULT(TRANSPOSE(compsPerProduct), IF(ISNUMBER(productQuantity), productQuantity, 0))
or a 365 version
= LET( cleanedVector, IF(ISNUMBER(productQuantity), productQuantity, 0), cleanedArray, IF(ISNUMBER(compsPerProduct), compsPerProduct, 0), MMULT(TRANSPOSE(compsPerProduct), cleanedVector) )
Oct 16 2023 06:29 AM