Calculate orders with all lines available after prior orders

Question

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

Just to be clear, the entries you give in your *remaining inventory up to current order* column are your final expected results? If not, can you clarify what they are? — Jos Woolley, Jul 17 '22 at 17:29
These are the remaining quantities after picking the quantities of the previous orders and the current order. This means that order `bb` does not have enough quantity for part `a` cat `1` since in order `cc` qty 2 was ordered already, so that left 4-2= 2. In order `bb` 3 are ordered, so every line of this order should be skipped. — P.b, Jul 17 '22 at 17:54
Ah, ok. So actually your final expected results don't appear anywhere within the post? — Jos Woolley, Jul 17 '22 at 18:10
Thanks. And your MMULT set-up, which you say failed on large datasets due to resource issues, nevertheless worked perfectly well on smaller datasets? If so, it would be worth sharing, in my opinion, since it demonstrates a working logic — Jos Woolley, Jul 17 '22 at 18:56
`=MMULT(--((ROW(H3:H8))>=(TRANSPOSE(ROW(H3:H8)))),L3:L8*(H3:H8=H3:H8)*(I3:I8=I3)*(J3:J8=J3))` would show the cumulative ordered qty for the same part / cat (range H3:M8 would be the sorted inventory by priority, like the second table in my question). The problem with this is that I could calculate this with the order available result, so it skips the orders with no inventory, but than in the cumulative result it still uses the value from the previous order. — P.b, Jul 17 '22 at 19:15
`=K3-SUMIFS(INDEX(L:L,3):INDEX(L:L,ROW()),INDEX(I:I,3):INDEX(I:I,ROW()),I3,INDEX(J:J,3):INDEX(J:J,ROW()),J3,INDEX(O:O,3):INDEX(O:O,ROW()),TRUE)` was used to get the `remaining inventory up to current order` in my question — P.b, Jul 17 '22 at 19:17
Great, thanks. One last question from me, as I'm still a bit confused as to what your expected results are (or at least how you derive them). You start by saying "*I'd like to calculate the **which orders** have enough inventory available to pick each order line in the order of the priority column*", but then you say that your expected results (presumably in order of the original table) are "*2, 1, 2, 1, 2, 3*". Could you clarify, perhaps explaning how you arrive at one or more of those expected results? — Jos Woolley, Jul 17 '22 at 19:20
Ah, I see the confusion. This is in order of the sorted data (so the second table). Logic explained would be that for each order (sorted by priority order) the available qty should be greater than the ordered qty. If so for each line if the order, subtract the qty ordered from each line of the order. If not enough available in one of the lines for that order, don't subtract any of these order lines and keep available for a lower priority order. — P.b, Jul 17 '22 at 19:26
Sorry, so for bb|a|1 you have an expected result of 2? But a|1 has qty available of 4, all of which is already taken by orders cc (qty ordered=2) and aa (qty ordered=2). So there is precisely zero left of a|1 - why is the expected result for bb|a|1 2, then? — Jos Woolley, Jul 17 '22 at 19:59
At prio order 1 `cc`all order lines have enough inventory available, so for part a cat 1 we then have 4-2 = 2. For prio 2 order there's not enough inventory to fulfill part a cat 2, therefore no order lines will be picked , so for part a cat 1 the available qty will remain 2. For the prio 3 order 3 are ordered from part a cat 1, and we have only 2 left. Therefore for this order no lines are fulfilled either and we end with qty 2 left of part a cat 1 — P.b, Jul 17 '22 at 20:14
I added an explanatory table at the end that may better explain the logic — P.b, Jul 17 '22 at 20:29
I think I have understood your logic, my questions are: 1) how you would like to present the desired outcome? Would you like to add two columns at the end of the first table, one showing if the order should be skipped and the other showing the remaining stock? 2) how many levels of priority in reality? 3) would you be open to a solution using the Power Query function in excel? — Terry W, Jul 18 '22 at 06:44
@Terry W 1) open to adding columns, but preferably not. 2) this differs, but can go up to 100! 3) definitely if that would help — P.b, Jul 18 '22 at 11:43
1) if adding columns is not preferred, how would you like to present the outcome? Do you want to show the true quantities in the ''qty available' column without indicating whether an order would be skipped? 2) with levels up to 100, I could not think of an efficient solution even using power query... maybe you could try asking the VBA guys 3) one more question, if within the same priority there are two orders, one can be fulfilled but the other cannot, what is the selection criteria in this scenario? — Terry W, Jul 18 '22 at 23:04
Good point about the orders with same priority. That would than be the order date, which is not listed in this example, but in this sample we don't need to take that into account. — P.b, Jul 19 '22 at 05:39

score 1 · Answer 1 · answered Jul 22 '22 at 18:22

I managed to get a working answer by combining formulas and VBA. Formulas to get the spill per order and VBA to workaround overwriting the spill causing spill errors:

Sub ordercalc()

Dim lr As Integer

Dim order As Range, part As Range, cat As Range, available As Range, ordered As Range, priority As Range

Dim i As Variant

Application.ScreenUpdating = False

With Sheet2  

    .Range("A2").Formula2R1C1 = _        
"=LET(a,sheet1!C1:C16,i,COLUMN(C[3]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("B2").Formula2R1C1 = _        
"=LET(a,sheet1!C1:C16,i,COLUMN(C[7]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("C2").Formula2R1C1 = _        
"=LET(a,sheet1!C1:C16,i,COLUMN(C[13]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("D2").Formula2R1C1 = _        
"=LET(a,sheet1!C1:C16,i,COLUMN(C[8]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("E2").Formula2R1C1 = _        
"=LET(a,sheet1!C1:C16,i,COLUMN(C[6]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("F2").Formula2R1C1 = _  
"=LET(a,sheet1!C1:C16,i,COLUMN(C[8]),s,SORT(a,{14,4}),FILTER(INDEX(s,,i),ISNUMBER(INDEX(s,,1))))"

    .Range("A1").Value = "order"
    .Range("B1").Value = "part"
    .Range("C1").Value = "cat"
    .Range("D1").Value = "available qty"
    .Range("E1").Value = "qty ordered"
    .Range("F1").Value = "priority"
    .Range("G1").Value = "Pickable"
    .Range("H1").Value = "Left after picking"

    lr = .Cells(.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lr

        If .Range("A" & i).Value <> .Range("A" & i - 1).Value Then .Range("H" & i).Formula2R1C1 = _
        "=LET(result,IFERROR(XLOOKUP(FILTER(R2C2:R" & lr & "C2,R2C1:R" & lr & "C1=RC1)&""|""&FILTER(R2C3:R" & lr & "C3,R2C1:R" & lr & "C1=RC1),FILTER(R2C2:R" & lr & "C2,(ROW(R2C1:R" & lr & "C1)<ROW())*(R2C1:R" & lr & "C1<>RC1))&""|""&FILTER(R2C3:R" & lr & "C3,(ROW(R2C1:R" & lr & "C1)<ROW())*(R2C1:R" & lr & "C1<>RC1)),R2C8:INDEX(C8,ROW()-1),RC[1]:R[1]C[1],0,-1),FILTER(R2C4:R" & lr & "C4,R2C1:R" & lr & "C1=RC1))," & Chr(10) & "ordered,FILTER(R2C5:R" & lr & "C5,R2C1:R" & lr & "C1=RC1),IF(SUM(N(result>=ordered))=COU" & _
        "NTA(ordered),result-ordered,result))"

        If .Range("A" & i).Value <> .Range("A" & i - 1).Value Then .Range("G" & i).Formula2R1C1 = _          
  "=LET(result,IFERROR(XLOOKUP(FILTER(R2C2:R" & lr & "C2,R2C1:R" & lr & "C1=RC1)&""|""&FILTER(R2C3:R" & lr & "C3,R2C1:R" & lr & "C1=RC1),FILTER(R2C2:R" & lr & "C2,(ROW(R2C1:R" & lr & "C1)<ROW())*(R2C1:R" & lr & "C1<>RC1))&""|""&FILTER(R2C3:R" & lr & "C3,(ROW(R2C1:R" & lr & "C1)<ROW())*(R2C1:R" & lr & "C1<>RC1)),R2C8:INDEX(C8,ROW()-1),RC[2]:R[1]C[2],0,-1),FILTER(R2C4:R" & lr & "C4,R2C1:R" & lr & "C1=RC1))," & Chr(10) & "ordered,FILTER(R2C5:R" & lr & "C5,R2C1:R" & lr & "C1=RC1),IF(SUM(N(result>=ordered))=COU" & _        
        "NTA(ordered),INDEX(""Yes"",SEQUENCE(COUNTA(ordered),,1,0)),INDEX(""No"",SEQUENCE(COUNTA(ordered),,1,0))))"   

    Next

.Range("G2:H" & lr).Value = .Range("G2:H" & lr).Value


End With

Application.ScreenUpdating = True

End Sub

It first sorts the data from a report to the priority, the it calculates the values left after order picking (if all order lines are available only), otherwise the qty left stays the same.

I also added a column to show if the order is pickable yes/no.

There's likely to be a better way to solve this either in VBA (likely) or Excel (lambda map maybe?), but this got me the result and I couldn't think anymore afterwards.

score 0 · Answer 2 · answered Jul 18 '22 at 06:56

0

This isn't the sophisticated answer you were looking for, but if I were doing it I would go back to basics - sort on part, cat, priority then just use a pull-down formula:

=IF(AND(B2=B1,C2=C1),IF(E2>=G1,G1,G1-E2),IF(E2>=D2,D2,D2-E2))

answered Jul 18 '22 at 06:56

Tom Sharpe

30,727
5
24
37

Thanks for your approach. This skips the fact that if any line from an order doesn't have enough inventory to fulfill that order, the other lines of the same order should be skipped as well. – P.b Jul 18 '22 at 11:47
Ah, should have read the question more carefully. Looks like it can't be done in one pass then. – Tom Sharpe Jul 18 '22 at 17:09
I thought the solution may be in combining both BYROW and SCAN, but I can't get my head around it. – P.b Jul 18 '22 at 18:37

Calculate orders with all lines available after prior orders

2 Answers2