I have the following data:
| order | part | cat | qty available | qty ordered | priority |
|---|---|---|---|---|---|
| aa | a | 1 | 4 | 2 | 2 |
| aa | a | 2 | 2 | 3 | 2 |
| bb | b | 1 | 3 | 3 | 3 |
| bb | a | 1 | 4 | 3 | 3 |
| cc | a | 2 | 2 | 1 | 1 |
| cc | a | 1 | 4 | 2 | 1 |
With this data I'd like to calculate the which orders have enough inventory available to pick each order line in the order of the priority column.
The data is in cells A3:F8, where A2:F2 are it's headers.
The qty available column shows the total available quantity of a product with a certain category (cat), so if all lines of that order are available the next order will have the available quantity minus the previously selected from orders.
I'm using Office 365 and used the following to calculate if all lines of an order are available:
=LET(data,SORT(A3:F8,{6;2;3}),
a,INDEX(data,,1),
d,INDEX(data,,4),
e,INDEX(data,,5),
BYROW(a,LAMBDA(x,SUM(N(FILTER(d,a=x)>=FILTER(e,a=x)))=COUNTA(FILTER(a,a=x)))))
The data part of the LET-function above sorts the original data's orders to it's priority to the following data:
| order | part | cat | qty available | qty ordered | priority |
|---|---|---|---|---|---|
| cc | a | 1 | 4 | 2 | 1 |
| cc | a | 2 | 2 | 1 | 1 |
| aa | a | 1 | 4 | 2 | 2 |
| aa | a | 2 | 2 | 3 | 2 |
| bb | a | 1 | 4 | 3 | 3 |
| bb | b | 1 | 3 | 3 | 3 |
The formula results in:
| Result |
|---|
| TRUE |
| TRUE |
| FALSE |
| FALSE |
| TRUE |
| TRUE |
If I would substract the ordered qty from the available qty from each order that has enough available according to the calculation result, I would have the following qty remaining.
| remaining inventory up to current order |
|---|
| 2 |
| 1 |
| 2 |
| 1 |
| -1 |
| 0 |
As you can see this means that for order bb, part a, cat 1 I would not have enough inventory after picking the orders with higher priorities, so all order lines of this order should be skipped as well and the remaining inventory would be usable for a later order if there was any.
I can't think of a way to iterate through the orders and it's remaining qty after substracting prior orders.
Would this be possible with LAMBDA maybe?
Or am I starting it all wrong and is there another way to calculate this maybe?
P.S. The actual data set contains thousands of lines. I tried MMULT with multiple conditions, but this resulted in a "not enough resources to calculate"-error.
This table may explain the logic of fulfilling an order a little further (data sorted in priority order):
| order | part | cat | qty available | qty ordered | priority | explanation | calc | remaining qty |
|---|---|---|---|---|---|---|---|---|
| cc | a | 1 | 4 | 2 | 1 | order cc all available, so available wty - ordered qty | 4-2 | 2 |
| cc | a | 2 | 2 | 1 | 1 | order cc all available, so available wty - ordered qty | 2-1 | 1 |
| aa | a | 1 | 4 | 2 | 2 | order aa part a cat 2 ordered qty > remaining inventory, so nothing gets delivered for any of the order lines | remaining 2 after order cc -0 | 2 |
| aa | a | 2 | 2 | 3 | 2 | order aa part a cat 2 ordered qty > remaining inventory, so nothing gets delivered for any of the order lines | remaining 1 after order cc -0 | 1 |
| bb | a | 1 | 4 | 3 | 3 | order bb part a cat 1 ordered qty > remaining inventory, so nothing gets delivered for any of the order lines | remaining 2 after order cc -0 | 2 |
| bb | b | 1 | 3 | 3 | 3 | order bb part a cat 1 ordered qty > remaining inventory, so nothing gets delivered for any of the order lines | 3-0 | 3 |
