Excel for Inventory Management

Hi, would appreciate any advice from the group.

I am using Excel to keep track of inventory. However, the current method of maintaining the file doesn't seem to be very efficient. Basically, I have a list of different SKU which are stored in different locations. When a certain SKU is used, the same quantity is replaced into the location where the SKU was withdrawn. The difference lies in the Lot No.

For instance,

If there is a stock OUT for 1x SKU1 in Shelf 2 Lot No. 123 -> I will manually delete this from the excel table I maintained. So if there is currently 2 nos, I will change it to 1.
Then do a stock IN the same SKU in Shelf 2 under a different Lot No. 345 -> create an additional row and add qty 1.
So quantity for SKU1 is always kept at 2.

Because it is a long list of 1000 odd items, I could lost count as to whether I had deducted the no. from the original quantity when doing stock out.

Am thinking if there is guidance on how to create an input card where stock in and out will be clearer. So assume an input card where I can type in SKU1, selection Lot no and Location to Remove it from Master File... and subsequently ADD a SKU into the master file.


I have attached a sample file for reference. Done some reading and it seems VBA is the only way to get this done but I am not familiar.. Thinking of creating an user form for data entry (input card) and am seeking guidance how I can make the form subtract or add to a master file.