I am managing a file with multiple orders. Each order contains some basic information like customer number, order number, SKU, and quantity ordered. I want to allocate available inventory to these orders, but have been running into some trouble. I have Sheet 1 with all my order data, and Sheet 2 is my inventory data. The issue is I have multiple orders for different customers for the same SKU so I want to allocate it down where it deducts from open inventory.
For example:
I have 10 orders for SKU 6789. 50 units total with 10 each on each order. Several customers.
I have 40 units of SKU 6789 in stock.
I want the file to show something like:
ORDER SKU QTY ORDER CAN SHIP
12345 6789 10 10
34565 6789 10 10
89475 6789 10 10
54678 6789 10 10
45265 6789 10 0
I would consider myself an intermediate excel user and I have been trying everything, and nothing is getting the desired result. Any help would be appreciated.
For example:
I have 10 orders for SKU 6789. 50 units total with 10 each on each order. Several customers.
I have 40 units of SKU 6789 in stock.
I want the file to show something like:
ORDER SKU QTY ORDER CAN SHIP
12345 6789 10 10
34565 6789 10 10
89475 6789 10 10
54678 6789 10 10
45265 6789 10 0
I would consider myself an intermediate excel user and I have been trying everything, and nothing is getting the desired result. Any help would be appreciated.