• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Do a product inside a spilled array

U

utud

New Member
Hello Excel experts,

I have a list that I obtained from a different sheet, using the filter function with multiple criteria. It is a list of all the materials contained in the BOM of various products with the correspondent quantity for each material.
The list goes like this: col A-material; col B-qty; col C-material; col D-qty, and so on.
Above this list I have a table with production orders and their qtys.
I would like to multiply the qtys in the spilled array with the ones in the production orders.
Is this possible without breaking the list into separate columns?
Thank you.
 
Probably, but I would need to see the exact calculation required before committing. You are not going to be multiplying anything by text material descriptions, so they could be removed by using
Code: 
= LET(
    shape, WRAPCOLS(TOCOL(BOM#),2),
    DROP(shape, 1)
  )
leaving a row of BOM quantities that could be multiplied by the order quantities
Code: 
= LET(
    shape,  WRAPCOLS(TOCOL(BOM#),2),
    matQty, DROP(shape, 1),
    productQty * matQty
  )
to give an array of results.

If the requirement were more specific you could use CHOOSECOLS to pick up a specific column of quantities within LET and assign a named variable to it. The general pattern then is to commit the first lines of code to identifying meaningful parts of the array by (local) name; the next section performs the set of related calculations. Finally, one uses HSTACK and VSTACK to block the results together for output. I prefer it if the formula fits comfortably on a single screen but passing blocks of calculation of to LAMBDA functions can reduce the formula length and keep it intelligible.
 
Last edited:
Peter Bartholomew said:
Probably, but I would need to see the exact calculation required before committing. You are not going to be multiplying anything by text material descriptions, so they could be removed by using
Code: 
= LET(
    shape, WRAPCOLS(TOCOL(BOM#),2),
    DROP(shape, 1)
  )
leaving a row of BOM quantities that could be multiplied by the order quantities
Code: 
= LET(
    shape,  WRAPCOLS(TOCOL(BOM#),2),
    matQty, DROP(shape, 1),
    productQty * matQty
  )
to give an array of results.

If the requirement were more specific you could use CHOOSECOLS to pick up a specific column of quantities within LET and assign a named variable to it. The general pattern then is to commit the first lines of code to identifying meaningful parts of the array by (local) name; the next section performs the set of related calculations. Finally, one uses HSTACK and VSTACK to block the results together for output. I prefer it if the formula fits comfortably on a single screen but passing blocks of calculation of to LAMBDA functions can reduce the formula length and keep it intelligible.
Click to expand...
So basically, I want to keep the material number, and material qty as well, and also it would be great if it was all one spilled array, as I want to copy the formula to other sheets with different products. From sheet to sheet the products vary and the number of materials contained by them also (some contain over 20 materials).
I will upload the file here, maybe there are other solutions for generating such a list in which I could calculate the product of the materials per production order.
The formula I tried is in sheet 40009101, cell B1000.
I would also like to transpose the result somehow on one row. The transpose formula doesn't work as it places the material numbers on one row, while the qtys are on the row below. I tried filtering the results from a query in the sheet called "Material consumption" as I have unpivoted the material numbers and qtys on a single column there, but the formula doesn't work if is filtered from the query.
As for the product problem, I wonder if it couldn't be done with IF and ISNUMBER.

Thank you.
 

Attachments

  • MTZ1 mat map.xlsx
    679.8 KB · Views: 5
What am I doing wrong?
Is this possible?

=TRANSPOSE(IF(ISNUMBER(FILTER(Material_consumption[Value],(Material_consumption[Work Center]='40009101'!$A$1)*(Material_consumption[Core]='40009101'!$P701)*(Material_consumption[Production Version2]='40009101'!C701)*(Material_consumption[R mat description]='40009101'!$B$999))),PRODUCT(FILTER(Material_consumption[Value],(Material_consumption[Work Center]='40009101'!$A$1)*(Material_consumption[Core]='40009101'!$P701)*(Material_consumption[Production Version2]='40009101'!C701)*(Material_consumption[R mat description]='40009101'!$B$999)),$D701),FILTER(Material_consumption[Value],(Material_consumption[Work Center]='40009101'!$A$1)*(Material_consumption[Core]='40009101'!$P701)*(Material_consumption[Production Version2]='40009101'!C701)*(Material_consumption[R mat description]='40009101'!$B$999))))
 

Attachments

  • MTZ1 mat map.xlsx
    679.4 KB · Views: 2
This is based upon your earlier workbook.
Code: 
= LET(
    ANDλ,          LAMBDA(x, AND(x)),
    rangeToSearch, HSTACK(Table1[Core], Table1[Production Version2], Table1[R mat description]),
    rangeToFind,   HSTACK(product, prodVTarget, R_Mat_Target),
    rangeToReturn, HSTACK(Table1[Component], Table1[Component Quantity]),
    returnRecord,  FILTER(rangeToReturn, BYROW(rangeToSearch = rangeToFind, ANDλ)),
    TOROW(returnRecord)
  )
 

Attachments

  • MTZ1 mat map.xlsx
    681.3 KB · Views: 6
thank you @Peter Bartholomew .
Got that sorted, the only thing that still evades me is how I could multiply the filtered qtys with the order qty inside the array.
Basically C1001 & D1001 with D701.
If I can't do this, the filtered array is useless to me
 
Although the alternating pattern of the output requested is awkward to use for ongoing calculation you could work within the formula using
CHOOSECOLS(returnRecord, 2)
which are the numbers still stacked vertically.

You could also include some calculations to return the corresponding order quantities within the same formula. That would allow you to perform the multiplication within the formula and stack the newly calculated results alongside those you already have.
 
Choosecols works great, but then I loose the material numbers, beside that if my array increases my formula needs tweaking again.
 
Last edited:
Solved.
I finally figured how to use the Let and Map functions, and they work wonders.
I will place the formula below if you ever use arrays with multiple columns where text is mixed with numbers, and you would want to do a calculation in that array.

=IFERROR( TRANSPOSE( LET(BOPMat,FILTER(Material_consumption[Value],(Material_consumption[R mat description]=$B$200)*(Material_consumption[Core]=$P103)*(Material_consumption[Production Version2]=$C103)*(Material_consumption[Work Center]=$A$1)), IF(ISNUMBER(BOPMat), MAP(BOPMat,LAMBDA(BOPMat,PRODUCT(BOPMat,$D103/100))),BOPMat))),"")
Thank you,
Couldn't have done it without you.
 
Last edited:
You must log in or register to reply here.
Back
Top