I am a newbie trying to develop an inventory management system.
Suppose I have two sheets. Sheet 1 contains the information about all the inventory in hand. Column A contains the information about the product ID and column B contains the information about the corresponding quantity available. EX: I sell three items so A column has three entries with product ID AA123, AB234, BC235 and the column B contains 3 entries with 23, 34, 45 which are the quantity available.
Now suppose in a day I sell 2 items AA123 (qty. 10) and BC235 (qty. 5). This entry is being made in sheet2 where column A contains the product ID and column B contains the qty. sold. I want to search the product ID of column A of Sheet2 with Sheet1 and subtract the qty. in sheet2 from sheet1.
The final sheet1 should look something like this (after sell has been made):
Column A: AA123, AB234, BC235
Column B: 23-10=13, 34, 45-5=40
Please suggest me some way out.