Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
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 :)
Hi Oliver :)
The formula you have given me is complicated beyond my ablities... but I was able to understand that you were referencing specific columns so I managed to adjust the formula in my file to grab the correct columns. And.... Eureka.... it works :)
Can we circle back to the formula for the "Status"? The previous formula was (which I understand it to be based on Supplier PO only):
=IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"closed",
IF(SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"open",
IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)>0,"in progress","")))I need the status to be determined by Seq no. / Supplier PO / SKU - I boxed in "PINK" to show what goes together and what the status should be.
Even though PO 1276348 repeats itself several times with several different products, the intention for "STATUS" is to "close" out when and incoming sequence # and outgoing sequence # net out to 0 for a specific product in question - Is this clear?
So, incoming # 1 and outgoing # 1 net out to 0 for 1276348 / A2620 SM BLU - "closed"
So, incoming # 2 and outgoing # 2 for 1276348 / A2620 SM BLU - "in progress"
So, incoming # 1 and outgoing # 1 for 1276348 / A2620 SM GRY - "in progress"
and so on...
Again, thank you!
Christine :)
Hello Christine,
thank you for your detailed explanation. I understand the task and the formula in cell K7 returns the results. The formula must be filled down. In the screenshot i've hidden some rows and highlighted 3 unique combinations of "Supplier PO" and "SKU" for illustration.
I've attached the sample file with 2 sheets with hidden and unhidden rows of the sample database.
Regards,
Oliver
=IF( OR( AND(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0), AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "closed", IF( OR(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<>0, AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7<>0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "in progress", "") )Hi Oliver :)
You've been great!!! I thank you for your help, and patience. This works.
A happy and satisfied user :)
Christine