Hello,
I have been tasked at the company I work for with keeping an eye out on our orders to be filled in the short term compared to our inventory level, in order to alert customers of delays as a temporary solution while a better software is implemented.
To do this, I have a list of orders we need to fill in the next 3 weeks with product code and quantity (~1200 lines of data), and I have a list of our inventory which includes product code, quantity and batch number. Here's a sample of the two sets of data that I have to work with : Dopbox link to image
That would be simple enough if it weren't for the fact that because of the nature of our business, we can't use multiple lots of the same product to fill one order. So if I have an order for product XYZ quantity of 100, and I have 200 in stock split the following way:
To explain what I'm trying to achieve, this is how I'm doing it manually today:
<code style="font-family: "Courier New", courier, monospace; margin: 0px 2px; padding: 15px; border: 0px; background-color: transparent; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 16px; overflow: auto;">=J2-SUMIF(A:A,I2,E:E)
</code>
On column A, I currently have the following formula:
=IF(VLOOKUP(B2,H2:K11,3,FALSE)>E2,VLOOKUP(B2,H2:K11,2,FALSE),"REVIEW")
This helps in looking at the first lot of product and if there's enough in it to fill the order, it selects that lot for the order. The problem is that I don't know how to force it to continue to look at other available lots if the first one does not have the required quantities. Using a nested if formula is not possible as some products could have as much as 50 different lots with different quantities.
Any help or guidance will be greatly appreciated!
I have been tasked at the company I work for with keeping an eye out on our orders to be filled in the short term compared to our inventory level, in order to alert customers of delays as a temporary solution while a better software is implemented.
To do this, I have a list of orders we need to fill in the next 3 weeks with product code and quantity (~1200 lines of data), and I have a list of our inventory which includes product code, quantity and batch number. Here's a sample of the two sets of data that I have to work with : Dopbox link to image
That would be simple enough if it weren't for the fact that because of the nature of our business, we can't use multiple lots of the same product to fill one order. So if I have an order for product XYZ quantity of 100, and I have 200 in stock split the following way:
- Lot A - Quantity 50
- Lot B - Quantity 50
- Lot C - Quantity 100
To explain what I'm trying to achieve, this is how I'm doing it manually today:
- On order sheet: Look at order line quantity
- On stock sheet: Find product, look for first available lot with order quantity or more. Copy lot number
- On order sheet: Paste lot number to order line (this will re-calculate stock quantity on stock sheet, based on a SUMIF formula)
<code style="font-family: "Courier New", courier, monospace; margin: 0px 2px; padding: 15px; border: 0px; background-color: transparent; border-radius: 2px; word-break: normal; display: block; font-size: 1em; line-height: 16px; overflow: auto;">=J2-SUMIF(A:A,I2,E:E)
</code>
On column A, I currently have the following formula:
=IF(VLOOKUP(B2,H2:K11,3,FALSE)>E2,VLOOKUP(B2,H2:K11,2,FALSE),"REVIEW")
This helps in looking at the first lot of product and if there's enough in it to fill the order, it selects that lot for the order. The problem is that I don't know how to force it to continue to look at other available lots if the first one does not have the required quantities. Using a nested if formula is not possible as some products could have as much as 50 different lots with different quantities.
Any help or guidance will be greatly appreciated!
