MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How many Kits Available


November 17, 2017 - by Bill Jelen

How many Kits Available

Today, an interesting Excel problem about bills of material. You have a lot of raw materials. Each item might be assembled into several different top-level assemblies. Based on the raw material on hand, do you have enough to fulfill an order for a certain item?


Watch Video

  • Tim asks: How many of each item is available to sell
  • Complicating factor: An item is comprised of multiple cartons
  • Bill Method #1: Add a helper column with INT(Qty Needed/On Hand)
  • Add Subtotals for the Min of Helper at each change in Product
  • Collapse Subtotals to the #2 View
  • Select all data. Use Alt + ; for Select Visible Cells
  • Paste to a new range
  • Ctrl + H to change Space Min to nothing
  • Mike Method #2
  • Copy the Product column to the right and use Data, Remove Duplicates
  • Next to the unique list of products, use MINIFS
  • Note that MINIFS is only available in Office 365
  • Bill Method #3: a regular pivot table fails because Calculated Fields won't work in this case.
  • Select one cell in your data and press Ctrl + T to convert to a table.
  • Instead, as you are creating the pivot table, choose the box for Add to Data Model
  • Create a new measure for Available to Sell using INT
  • Create a new measure for Kit Available to Sell using MINX
  • That pivot table works!
  • Mike Method #4 Use AGGREGATE function.
  • It seems like you would want to use MIN argument, but use SMALL because it handles arrays
  • Use =AGGREGATE(15,6,INT($D$2:$D$141/$C$2:$C$141)/($A$2:$A$141=F2),1)
  • AGGREGATE is one of five functions that can accept an array as an argument without Ctrl + Shift + Enter
  • Bill Method #5
  • Convert the data to a table and use Power Query - aka Get & Transform
  • In Power Query, calculate OH/Needed
  • Use the Number.RoundDown function to convert to integer
  • Use Grouping by Part Number and Min Avail
  • Close & Load
  • Bonus: It is refreshable!

Auto-Generated Transcript

  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelen
  • from MrExcel be joined by Mike Kerwin
  • from excels phone this is our episode
  • 190 how many kits are available to sell
  • all right today's question Assen and by
  • Tim watches our dueling excel videos he
  • works for a retailer and asked to create
  • a spreadsheet it to show our sales team
  • what we own and what we can sell sounds
  • simple right but here's the catch the
  • item they sell contains multiple cartons
  • and are inventoried on a per carton
  • basis here's an example what he's saying
  • seeing so here's this item P one two
  • three four five has three different
  • things that they have to ship and in the
  • kit requires four of Cardon one one of
  • card in two in one of carton three and
  • this is how many they have in stock
  • alright so just doing the math here they
  • have two complete sets of carton one for
  • complete sets of carton two and three
  • complete sets of carton three but that
  • means the what they can sell is the
  • minimum of those three numbers like on
  • my cell - and here they have four
  • complete sets of carton for four of
  • carton five two of carton three only one
  • of carton seven that's the limiting item
  • so in this case they can only sell one
  • of these all right now a question for a
  • later day I said well is there any
  • chance that carton 3 is used in more
  • than one place and he says yes but we're
  • gonna worry about that later
  • alright so here's how I'm gonna attack
  • this I can actually think of several
  • different ways to attack this so this
  • might be interesting this might be a
  • back and forth type of duel what I'm
  • gonna do is I want to have a helper
  • column out here and the helper column is
  • gonna look on an item-by-item basis of
  • how many we can sell so equal eight
  • divided by four like that and we'll
  • double click - to copy it down but let's
  • say that we needed four and we had six
  • alright so now it's gonna say 1.5 well
  • you can't sell you know half a couch
  • alright so it's going to have to be the
  • whole number so what I'm gonna do here
  • is use the int int the integer that
  • thing which will take off the decimals
  • and leave us just
  • all right so then we have eight back to
  • the original number and we need to
  • figure out for each item here what's the
  • smallest number over and column e make
  • sure the data sorted by product go to
  • the data tab choose subtotals at each
  • change in product use the min function
  • you know I teach subtotals all the time
  • of my power Excel seminars and I point
  • out there's 11 functions here but I've
  • never used anything other than sum and
  • count
  • so while subtotal may not be the fastest
  • way to do this I want to be able to say
  • that there was actually one time where I
  • was able to use something other than sum
  • and count all right click OK and we're
  • gonna get is every time the curtain
  • number the product number changes we get
  • to see them in and that min is the
  • answer that we want so I collapsed down
  • to the number two of you I'll select all
  • of this data and alt semicolon to select
  • just the visible cells ctrl C and then
  • we'll come down here and paste let's
  • just paste out to this area control V
  • alright delete the extra columns and
  • then we have to get rid of the word min
  • and not just the word min but space min
  • alright so I'm gonna use control H and
  • change the recurrence of space min to
  • nothing my place all click OK click
  • close and there's our table what we have
  • available to sell all right Mike throw
  • it over to you Wow
  • MrExcel I love it the min function in
  • subtotals how cool is that
  • alright I'm gonna go over to this sheet
  • right here I'm gonna do the same helper
  • column int will take on hand / rec
  • required quantity close parentheses
  • control enter double click and send it
  • down now I just need to find the men
  • available for a given condition or
  • criteria I'm gonna select product
  • control shift down arrow control C to
  • copy then I'm gonna right arrow control
  • V then I'm gonna come up and say remove
  • duplicates there it is I used to use
  • advanced filter unique wreck
  • it's only all the time but it seems like
  • this method is faster
  • there's my unique list now I'm gonna
  • come over here how many and I'm gonna
  • use the new function man yeah snowmen
  • ifs is in office 365 for Excel 2016 or
  • later the min range well I need to find
  • the minimum value in this column control
  • shift down L f4 comma and the criteria
  • range that's gonna be this whole product
  • control shift down arrow f4 comma left
  • arrow and there we go that will get the
  • min value from how many based on the
  • condition or criteria close parentheses
  • control enter double click and send it
  • down all right so there's min ifs and
  • subtotal I'm gonna throw it back over to
  • you MrExcel yes Mike very nice remove
  • duplicates kdeeny list of products and
  • then the min ifs function
  • I asked him what version of Excel he's
  • on he said Excel 2016 I hope it's office
  • 365 version of 2016 so he has access to
  • that well how about a pivot table all
  • right so I created a pivot table with
  • product and requires some are required
  • quantities some of them on hand then
  • from right here analyze fields items and
  • sets calculated field and created a new
  • calculated field called available which
  • is on hand / required quantity that way
  • I don't need the help or column over
  • here and it first it seemed like it was
  • gonna work because we had two three and
  • four and the reporting that the minimum
  • is two I change this calculation of
  • course to min and that seemed good but
  • then on this one where we have two four
  • four one two it's reporting three and
  • what's happening is it's doing the
  • calculation on this row we have 25 on
  • hand divided by eight that's three in a
  • fraction and so it's reporting three and
  • so no a regular pivot table calculate
  • item is not gonna work but instead
  • convert this data to a table and an
  • insert a pivot table add this data to
  • the data model click OK
  • and we're gonna have down the left-hand
  • side product and what it requires I'm
  • gonna create two implicit measures here
  • with a required quantity and some of on
  • hand and then I'm gonna create a new
  • measure so PowerPivot measure a new
  • measure and this new measure will be
  • called available to sell and that
  • formula is going to be how many we have
  • on hand divided by how many are required
  • for each item and click OK
  • alright so 8 divided by 4 is 2 all right
  • now that's still not our right answer
  • and we probably need to run this through
  • the the integer function so measures
  • where I manage our measures edit this
  • and wrap the whole thing inside the int
  • function like this click OK and click
  • close now we're getting a fractional
  • number still the wrong answer here but
  • we're gonna use a great new function
  • that's only available in Dax new measure
  • and this is gonna be called kit
  • available and the function is not min
  • but min X min X the Minx function and
  • the table that we use is Table one and
  • then expression is going to be that
  • available to sell that we just
  • calculated and what this does the min X
  • function evaluates on a row by row basis
  • and finds the minimum error and so will
  • click kit available okay I'll check this
  • out so here where we have two four four
  • one and two it's reporting one all right
  • now in a perfect world all we have is
  • product and kit available we don't need
  • any of this other stuff in the middle
  • all right so we're just gonna check this
  • here 2 1 3 2 are our answers I'll take
  • the requires out 2 1 3 2 yes it's gonna
  • work we actually take all the
  • intermediate calculations out
  • just have a kit available like that Mike
  • do you have another one how cool is that
  • MrExcel you use the Minx function in
  • Dax well I'm gonna go back over here I'm
  • gonna use a formula but I'm gonna
  • pretend like I don't even have this help
  • or column
  • I used min ifs well before men ifs in
  • Excel 2016 there was the aggregate
  • function in Excel 2010 now I want to use
  • man but of course functions 1 to 13 do
  • not let you do array formulas so I'm
  • gonna have to use small one as a
  • substitute for the min function and
  • small is one of the functions 14 and
  • above that can handle array operations
  • that argument right there array so
  • function number 15 comma I want to
  • ignore divided by zero error so I'm
  • going to type a 6 to ignore errors comma
  • and I need to simulate that whole helper
  • column in the array argument I am T and
  • instead of simply saying on hand divided
  • by require we do the whole column
  • control shift down arrow f4 divided by
  • the required column control shift down L
  • f4 now close parenthesis that int right
  • there if I highlight this and hit f9 it
  • simulates that entire how many helper
  • column control Z now I simply divided by
  • in parentheses I need to get an array of
  • trues and falses so I click on product
  • control shift down f4 and I ask the
  • question are any of you equal to that
  • product ID close parentheses that will
  • give me a bunch of trues and falses f9
  • trues and falses in the denominator true
  • will become a 1 false will become a 0
  • which will give us divide by zero error
  • control Z in essence if I click the
  • whole array in here
  • f9 the divide by zero is going to be our
  • filter so we only see the numbers for a
  • particular product control Z and then of
  • course aggregate will pick the min out
  • from that array of errors and numbers
  • close parenthesis and aggregates amazing
  • one of five functions that has an
  • argument that can handle array
  • operations without control shift enter
  • so I simply control enter and f2 what
  • did I forget
  • backspace array then I type a comma and
  • the K is 1 because I always want small
  • one which is the min close parentheses
  • control enter double click and send it
  • down f2 all right aggregate with that
  • whole helper column right there to get
  • how many for each product all right I'm
  • gonna throw back over to MrExcel hey
  • that's beautiful I knew there'd be a lot
  • of different ways to solve this I did
  • not think of using aggregate which of
  • course is better because if someone has
  • 2010 this will work the 15 allows an
  • array out here that is gorgeous
  • all right now hey when I set up the
  • question I just missed this and you know
  • and and Mike you know this when people
  • send us questions they try and minimize
  • the situation to make it sound like it's
  • easy but the thing that's gonna be a
  • disaster here is the fact that carton 3
  • is used in multiple places alright and
  • as soon as they sell something from
  • let's say they sell like this item P 1 2
  • 3 4 6 well then the number of carton
  • threes on hand it's gonna change right
  • and so that's gonna impossibly impact
  • what else we can sell alright so
  • thinking about how Tim is gonna have to
  • manage this process he's gonna have to
  • wait I have a way to regenerate this
  • this item quickly and so hopefully he
  • has an inventory table for every item
  • it'll show how many there are on hand
  • and then a vlookup here to pull the
  • inventory over alright that's what I'm
  • hoping is going to happen because then
  • it might become somewhat manageable and
  • if this is something we have to
  • reproduce again and again and again
  • then power query definitely has a use
  • here so power query in Excel 2010 or
  • 2013 you're gonna go download it you'll
  • have your own power query tab but in
  • Excel 2016 you're gonna look for the get
  • and transform it's funny in Excel 2016
  • it was the second group but then in
  • office 365 they moved it to be the first
  • group the power query has the ability to
  • take something from a table or range so
  • I'm gonna choose one cell in this table
  • ctrl T that will create a table for me
  • table 3 is a fine name I don't need to
  • rename that now this is a table we go to
  • data from table or range and we are
  • going to add a new column this column is
  • gonna be a custom column it's gonna be
  • called available and that is going to be
  • the on hand divided by required quantity
  • all right now we need to send this into
  • the I and T function and for slaying the
  • functions and power query are not the
  • same so click here and then go to
  • formula types and you'll find this
  • function is called number dot round down
  • and this is case sensetive you have to
  • make sure to use that exact same case so
  • equal number dot round down open paren
  • and closed paren and we click ok and so
  • 11 divided by 4 is two point seven five
  • rounds down to two alright that's the
  • answer we need there we don't need these
  • columns anymore site click on requires
  • shift click on on hand and remove those
  • columns all right now choose product
  • transform group by or a group by the
  • product and the new function is going to
  • be called kits available and the
  • operation is going to be the min of the
  • available column click OK alright so now
  • we have product and kits available home
  • close and load get a brand new sheet
  • with our answers but here's the
  • beautiful thing all right so when we
  • sell something let's make these columns
  • less wide and we sell let's say we sell
  • enough so we have no curtain threes left
  • I change that number there
  • that vlookups bring the results and then
  • come back here and choose this and
  • refresh all and you see that now we have
  • none of this and this and this available
  • to sell because they all needed that
  • garden 3 and we have none of those left
  • being able to refresh that by our query
  • is going to help this in the end well
  • this was a fun one
  • for me because I knew there would be a
  • lot of different ways to solve this
  • problem the the episode wrap up of this
  • really long episode how many of each
  • item is available sell and there's
  • multiple cartons alright so the first
  • thing I did was add a helper column and
  • then use subtotals with the min function
  • and then a whole bunch of really boring
  • steps micro method number two used min
  • ifs which is great if you have office
  • 365 I went back to a pivot table but a
  • regular pivot table won't work instead
  • have to do a data model and then use the
  • Minx function the min x function and
  • that calculated field or measure will
  • actually work Mike using the aggregate
  • function beautiful function one of five
  • functions that can accept an array as an
  • argument without control shift enter and
  • then method 5 convert the data to a
  • table and use power query also known as
  • get and transform and we're gonna
  • calculate on hand divided by needed and
  • then the number dot round down function
  • to convert to an integer group by part
  • name number and calculate the minimum
  • available close and load in the bonus
  • it's refreshable well hey i want to
  • thank you for stopping by we'll see you
  • next time for another dueling excel
  • podcast from MrExcel and excel is fun

Download File

Download the sample file here: Duel190.xlsx

Title Photo: minarikovamichaela87 / Pixabay


Bill Jelen is the author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.