Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
Hello,
I wonder if anyone here can help me. I have created an inventory tracking file in excel but I have encountered a difficulty that I cannot seem to find an answer for.
I have sequenced each incoming shipment but I am unable to sequence or match or track outgoing shipments automatically based on specific conditions:
- I need to assign #1 to outgoing shipments until outgoing piece count of 25 is reached, then #2 until 20 is reached and so on
- When incoming and outgoing piece count net out to 0, I need the status to be "Closed", when we've started pulling from but not emptied out yet, "In progress", and when untouched "Open"
I would like outgoing to be link to earliest, not closed, incoming shipment.
Of concern: "Out" and "Status" columns
Can anyone help with this?
Thank you!
Christine
2 Replies
=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","")))This is the formula in cell G2.
=IF(A2<>"","",INDEX($A$2:A2,LARGE(IF($A$2:A2<>"",ROW($A$1:A1)),1)))This is the formula in cell B2. The formula must be entered as an arrayformula with ctrl+shift+enter if you work with legacy Excel such as Excel 2013.
Both formulas must be filled down.
Here's a VBA solution proposal, paste the data into the file (I can't paste it from your image, it's too time-consuming) and click the button.
It should work🙂.
My answers are voluntary and without guarantee!
Hope this will help you.
