Raw Materials Needed to Build Widgets!

KVarik recently asked a question about how to figure out how many parts are needed to create different individual items.  After discussing the issue, I soon realized that the outcome could be very valuable to anybody.  After reading this,feel free to use the template located here.

While I'm not exactly sure what KVarik was using it for I'll explain the template and the use in terms of a production line.  Let’s assume that Ted Company makes different kinds of Items.  Each Item is made from a combination of 10 different parts. I only have 10 different kinds of parts currently but the template can calculate up to 21 different parts.  Currently the template also only shows 10 different items but the number of items that can be applied is infinite.

So the spreadsheet allows me to decide how many of each type of Item I want to make and how many of each part I will need to make each item.  In order for this to work a user must indicate how many of each part are needed to make each Item.

One might think the solution was simple but keep in mind that each Item is composed of a different number of each part.  This means that the system had to be able to know how many of each part were needed for each Item and how many Items were being made.  I accomplished this by using a modification of Adam's arrayformula and vlookup combination.

Here is the function: =arrayformula(iferror($B3:B*vlookup(A3:A,'Item List'!A2:Y,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}*sign(row(A3:A)),FALSE)))

Now if you read the other post about using arrayformula and vlookup then this looks very similar with only two changes.  The first change I made was to the first parameter of the if statement.  Adam/Ahab's method creates a header row for the first row by explicitly defining what is going to go in each column header.  In this case the column headers are defined by other means so I only want to start the matching and multiplying process.  Therefore, I completely got rid of the nested if statement and created one iferror statement.  I still use the iferror() trick which involves only including the first parameter. Remember, that by excluding the second parameter, the function returns blank cells when it errors (in this case if doesn't find the item in the Item List).

The second change is that I added “$B2:B*” before the vlookup.  Think back to the days of linear algebra.  If you want to multiply a matrix (or array) by a value you simply indicate that value on the outside of the matrix (or array).  In this case I want to multiple the key array for each Item by the quantity I want.  So my output will look something like this:

$B3:B*{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}

Where $B3:B is the quantity of the Item

Where {2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22}is the key array from the Item List for the Item type in column A.

I suggest opening the template, making your own copy and changing the quantity on the Manifest tab.

Let me know if you have any questions about the template and have fun! 

Note: my normal convention is to indicate fields that can be edited without disruption of the spreadsheet with a yellow background and fields that should not be edited have a grey background.

Cheers!