match numbers based on conditions and show the difference

i am making a personal stock keeping workbook. i get packages of different materials containing items of different weights. like i get a box of copper tubes containing 10 tubes each having different weight. like wise for iron and other materials.

now i have made a sheet where i feed the name of item using vlookup and the enter the details of tubes in each package. a similar sheet for tubes sold. i am looking for a formula which would reference the inward sheet, sort all data for one particular product like copper, take all the different weights and then subtract the ones in the outward sheet and display each individual weight in the stock in hand in a separate sheet.

each product will be entered on daily basis so a certain product will have multiple entries in both inward and outward sheet in no particular sequence with different dates and different weights. the formula should be able to generate the stock in hand on the fly for each product by comparing both inwards and outwards as and when data is entered in inward or outward sheet.

thanks

Originally Posted by JeteMc

This proposed solution employs two helper columns (which may be hidden for aesthetic purposes), one on the inward sheet and one on the outward.
The formula that populates the helper column (match) on the inward sheet is: =IF(H8="","",ROW()-7)
The array entered formula* that populates the helper column (match) on the outward sheet is:

Formula:

=IF(H8="","",SMALL(IF(INWARD!E$8:E$4002=E8,IF(INWARD!H$8:H$4002=H8,ROW($8:$4002)-7)),COUNTIFS(E$8:E8,E8,H$8:H8,H8)))

Note: on both the inward and outward sheets the formulas that populate columns E and G have been modified.
The array entered formulas* that populates the columns on the Stock In Hand sheet are all similar to:

Formula:

=IFERROR(INDEX(INWARD!H$8:H$25,SMALL(IF(INWARD!E$8:E$25=B$3,IF(ISNA(MATCH(INWARD!J$8:J$25,OUTWARD!J$8:J$25,0)),ROW($8:$25)-7)),ROW(1:1))),"")

*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode.
Let us know if you have any questions.

first of all thanks a lot. the output works as expected

but in doing so it breaks a few things, namely

1) when i enter a item number in column D it used to display the name of the product from pid master using vlookup automatically. now its not working
2) When there was a entry in the column E column G used to start totally automatically from the blank cell in column H till the last blank cell. Its also not working anymore.

can you please find a fix for the same

thanks a ton!

Originally Posted by JeteMc

Point one seems to remedied by modifying the formula in E8 and down so that it reads:

Formula:

=IF(AND(D8="",H8=""),"",IF(ISNA(VLOOKUP(D8,FABDUPLIST,2,FALSE)),E7,(VLOOKUP(D8,FABDUPLIST,2,FALSE)))) 

Point one seems to remedied by modifying the array entered formula in G8 and down so that it reads:

Formula:

=IF(D8="","",SUM(INDEX($H$8:$H$101,MATCH("zzzzz",$E$8:E8,1)):INDEX($H$8:$H$101,MATCH("zzzzz",$E$8:E8,1)-1+INDEX(FREQUENCY(IF($H$8:$H$101,ROW($H$8:$H$101)),IF(NOT($H$8:$H$101),ROW($H$8:$H$101))),COUNTIF($H$7:H7,"")))))

Let us know if you have any questions.

your solution fixes the problems i mentioned but breaks the stock output in stock in hand sheet!!!

here is my file with your formula applied. it does not update the stock in hand sheet