# Need a burn down formula



## Davbolmac (Dec 9, 2022)

Hi All. 
Im trying to find a formula to help me skip a batch no that doesnt have enough stock.
In the below example, i will have a stock sheet telling me the batch number and batch quantity and part number.
On a separate sheet i will have a demand (pick sheet) that someone will take to a shelf to get the part.
On the first part a simple Vlookup will show me BN12, but because there isnt enough stock in BN12 to fill the second line, id like the report to change the batch to the next one that has enough to fill the demand.
I'll also be using this for possibly hundreds of lines with multiple part numbers.

Any help is appreciated.


----------



## hajiali (Dec 16, 2022)

Try the below. this will list all locations of the part number entered in G4 regardless of the demand. hope this helps

Book1.xlsxABCDEFGHI1Part NoBatch No.Batch QtyPart No.BatchDemand212345BN12112345BN121312346BN131BN151412347BN141 512345BN151 6 7 8 9 10 Sheet1Cell FormulasRangeFormulaH2:H10H2=IFERROR(INDEX($B$2:$B$5,AGGREGATE(15,6,(ROW($B$2:$B$5)-ROW($B$2)+1)/($A$2:$A$5=$G$2),ROWS($H$2:H2))),"")


----------



## AhoyNC (Dec 17, 2022)

Another option would be to use the FILTER & SORT function to show the locations and qty at each location.
Book3ABCDEFGHIJ1Part NoBatch No.Batch QtyPart No.DemandBatchBatch Qty212345BN121123451BN121312346BN1311BN152412347BN141512345BN152Sheet4Cell FormulasRangeFormulaI2:J3I2=SORT(FILTER($B$2:$C$5,$A$2:$A$5=$G$2,"No Match"),2,1)Dynamic array formulas.


----------

