I am a manufacturer of 50+ products and each product requires multiple/different raw materials to manufacture. I need to automate the below:
1. When a new order is received and entered in the excel, it should look into the materials required and calculate the required quantity from a material data sheet to manufacture the order
2. The required material to prepare the order has to be compare with the Material inventory and get the output, if the materials are in stock or need to be ordered to manufacture the current order.
I tried preparing this using the Vlookup, but the materials which are not used for this order is also displaying 0 and the column length is getting increased.