juniorkrebs
New Member
- Joined
- May 24, 2019
- Messages
- 1
I don't even know how I'll explain this for you, here's the situation:
Everyday at work we receive a "recipe" with the amount of ingredients we'll need to use that day to manufacture our products (we use the FEFO system to pick the ingredients). Today there's a person who compares the quantity needed in the recipe with our warehouse inventory, than he fills a worksheets with every ingredient and their corresponding batch numbers (i.e. we're going to need 100kg of calcium carbonate, he writes "calcium carbonate - 100 kg - 4 bags - 01234/01235/01236" (these numbers are the batch numbers from the calcium carbonate). This worksheet is used as a reference to other employees who are responsible to find and pick these batches in the inventory to make the weighting processes quicker and easier. Also, there's two kind of ingredients: those that are in use and those that are new, I already made a VBA script to classify them and put each kind in a different worksheet.
Ok, history told, now let's go to the real problem:
You've noticed that the batch numbers are a manual input, right? I want to change it and make it automatic, so I use VLOOKUP to find the batch that is in use and the next one (the new) if the batch in use doesn't have the amount needed. The problem is, how can I find the next batches if the first new one doesn't have the quantity I need? I can't use INDEX+MATCH because sometimes we don't have that ingredient anymore and then the formula would return me the batch from another ingredient.
Suppose I have to use 100 kg of calcium carbonate again, the batch that is in use has 27 kg, in order to complete the 100 kg I'll have to look at my inventory and pick the next based on FEFO, so, I'll need 73 kg from the "new" ingredients, the problem starts when the next new batches are less than 73 kg, because I'd have to list more than one batch number.
I thought that I could solve it with some kind of SUMIFS, but I couldn't figure out how to implement it.
My rule would have to be something like this: if (inventory - amount of the recipe)>0 than it would list the batch in use, otherwise it would have to list every new batch used in the sum (sometimes it could be just one, sometimes it could be four, maybe ten) until the sum result becomes a positive number.
So, how do I do it? It looks impossible to me.
Everyday at work we receive a "recipe" with the amount of ingredients we'll need to use that day to manufacture our products (we use the FEFO system to pick the ingredients). Today there's a person who compares the quantity needed in the recipe with our warehouse inventory, than he fills a worksheets with every ingredient and their corresponding batch numbers (i.e. we're going to need 100kg of calcium carbonate, he writes "calcium carbonate - 100 kg - 4 bags - 01234/01235/01236" (these numbers are the batch numbers from the calcium carbonate). This worksheet is used as a reference to other employees who are responsible to find and pick these batches in the inventory to make the weighting processes quicker and easier. Also, there's two kind of ingredients: those that are in use and those that are new, I already made a VBA script to classify them and put each kind in a different worksheet.
Ok, history told, now let's go to the real problem:
You've noticed that the batch numbers are a manual input, right? I want to change it and make it automatic, so I use VLOOKUP to find the batch that is in use and the next one (the new) if the batch in use doesn't have the amount needed. The problem is, how can I find the next batches if the first new one doesn't have the quantity I need? I can't use INDEX+MATCH because sometimes we don't have that ingredient anymore and then the formula would return me the batch from another ingredient.
Suppose I have to use 100 kg of calcium carbonate again, the batch that is in use has 27 kg, in order to complete the 100 kg I'll have to look at my inventory and pick the next based on FEFO, so, I'll need 73 kg from the "new" ingredients, the problem starts when the next new batches are less than 73 kg, because I'd have to list more than one batch number.
I thought that I could solve it with some kind of SUMIFS, but I couldn't figure out how to implement it.
My rule would have to be something like this: if (inventory - amount of the recipe)>0 than it would list the batch in use, otherwise it would have to list every new batch used in the sum (sometimes it could be just one, sometimes it could be four, maybe ten) until the sum result becomes a positive number.
So, how do I do it? It looks impossible to me.
