Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
Hi Oliver,
The formula works! Thank you :)
But... I just noticed that the outgoing assigned # is not accurate.
I would like outgoing orders to be assigned to the earliest Incoming # first as per image below - I used red arrow to illustrate my point
Each incoming skid has its own #, as we ship out, I need to remove from first incoming skid, then the 2nd, and so on.
Is there a solution for this?
Again, thank you,
Christine
Hello Christine,
you are welcome.
In your latest screenshot the scenario seems to have changed completely and unfortunately i'm not sure what you want to do.
Perhaps the screenshot details represent your expected result. Do the Skid 1, Skid 2 .... show what you mean by skid and does range E4:E24 show the intended output? Perhaps we only need to sequentially count how often "out" occurs within each skid.
=LET(_num,SCAN(0,C4:C24,LAMBDA(a,b,IF(b="out",a+1,0))),IF(_num=0,"",_num))Please let me know what Excel version you work with. This suggestion works with current Excel online and Microsoft 365.
Kind regards,
Oliver
Oh crap.... my apologies for my communication skills....
HI Oliver :)
The scenario has not changed, but I did re-sort after I inserted your formula solutions. In doing so, it dawned on me that I had not shown you enough of my table to show you the incoming sequencing pattern. I also showed you more columns then in my original post which were hidden.
In column E, I would like outgoing pieces to be applied to incoming skids in order of sequence. As an example, outgoing 60 pieces should be applied to incoming # 1 in D7. Once outgoing pieces reach the 240 pieces , then I would like next outgoing pieces to pull from #2, and so on.
My red arrow is tracing the result of the formula you provided and the result I would like.
Each order "in" can have a number of different products - The incoming sequence is based on (2) conditions; Supplier PO and SKU
So in my table:
Order 1276348 had 4 different products (SKUs) therefore the sequencing is:
A2619 SM BKMT ; 1,2,3
A2619 SM BLK: 1,2,3,4,
A2619 SM BLP 1,2,3
A2620 SM BKW: 1,2
To make this pattern more clear, or more obvious, I added a few more lines of my table. Order 1275621 has to outgoing orders, the 26 pieces should be applied agains #1, and the 4 outgoing pieces should be applied against #2 because #1 nets out to 0
I hope this is clearer. I do thank you for your patience and your help. I would be so much easier if I could send you my file.
I use Microsoft 365 - Windows 11
Christine
Hello Christine,
thank you for your detailed explanation. Unfortunately i didn't understand earlier what should be done.
This formula spills the results. However the LAMBDA REDUCE VSTACK has some limitations therefore i've added basically the same formula in cell M7 of the attached sample file. The formula in M7 must be filled down and it doesn't have the limitations of the LAMBDA REDUCE VSTACK formula.
=LET(_data,CHOOSECOLS(B7:AB66,1,3,6,9,21,27),DROP(REDUCE("",SEQUENCE(ROWS(_data)), LAMBDA(w,x,VSTACK(w,IF(INDEX(_data,x,1)="in","",LET(_rng,TAKE(_data,x), _filter,FILTER(_rng,(CHOOSECOLS(_rng,3)=INDEX(_rng,x,3))*(CHOOSECOLS(_rng,4)=INDEX(_rng,x,4))), _sequence,SEQUENCE(-SUM(TAKE(_filter,,-1))), _totalin,ROUNDUP(_sequence/INDEX(_filter,1,5),0), UNIQUE(TAKE(_totalin,INDEX(_rng,x,6)))))))),1))Kind regards,
Oliver
Revised: Line 53 in "Open - Out", Seq # s/b a 2 not a 1
Chrsitine :)