Summarising a spreadsheet with multiple like terms

Hi, I'm pretty new to macros and more complicated formulae so if anyone could give me any suggestions or alternative options for this I would be extremely grateful.

I am building a stock register and currently have a sheet for stock coming in which is logged as a part number and quantity added in as and when the stock arrives and a sheet for out which is the same. This creates multiple entries relating to the same part number.

It is easy to interrogate these sheets to get a current stock level for a particular part number using SUMIF but how would I make a summary sheet which showed all part numbers in the other two sheets but only once and with the current stock level next to them? It would need to reflect new product numbers as they are added to the 'in' sheet as well.

To show an example:

A sample of the 'In' sheet looks like this:

NUMBER QTY
4353026733-041 2
4353038733-041 2
4353051733-041 47
4353064733-041 51
4353076733-041 23
4353026733-041 2

A sample of the 'out' sheet looks like this:

NUMBER QTY
4353026733-041 1
4353051733-041 4
4353076733-041 23

And I want the summary sheet to look like this:

NUMBER QTY
4353026733-041
4353051733-041 43
4353076733-041 0 (This zero can either be shown or not depending how it works)

If a new product is added to the 'in' sheet it should automatically show up in the summary sheet and ideally (but not necessarily) disappear again when it is out of stock. I don't mind having to manually update the summary sheet after adding something by using a macro button for example, I just want to get around constant copying, pasting and hunting for product numbers in a list while still having a visible record of everything that is currently in stock.

I hope that all makes sense.

Thank you very much in advance.

Try this for Results on sheet "SUMMARY"

Please Login or Register  to view this content.

Regards Mick