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
19 Replies
This one is revised - line 53 in "Open - Out", sequence # s/b a 2 not a 1 as above
create temp table aa as
select *,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2;
select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa;
select * from Sheet2;
create temp table aa as
select rowid old_rowid,*,sum(`Total pieces`+`Total out`) over (partition by `Supplier PO`) Balance from Sheet2;
create temp table bb as
select *, case when Balance=0 then "closed" when Balance=`Total pieces` then "Open" else "In Process" end Status from aa;
create temp table cc as
select row_number() over () in_out,`Supplier PO` from bb group by `Supplier PO`;
select iif(`Total out`<0,'',in_out) `In`,iif(`Total out`<0,in_out,'') Out,* from cc join bb using(`Supplier P
O`);
Auto allocate First In First Out batch number:
1,in running balance
2,out running balance
3,match in and out balance
select * from Sheet2 limit 20;
create temp table aa as
select rowid old_rowid,*,row_number() over ( partition by SKU,PO) batch,sum(Pieces) over(order by rowid) in_balance from Sheet2 where Pieces>0;
create temp table bb as
select rowid old_rowid,*,sum(Total_out) over(order by rowid) balance from Sheet2 where Pieces=0;
select * from aa;
select * from bb;
create temp table cc as
select *,(select first_value(b.batch) over () from aa b where b.SKU=a.SKU and b.PO=a.PO and b.in_balance>=-a.balance) in_batch from bb a;
select * from cc;
select Sheet2.rowid,Sheet2.*,aa.batch,cc.in_batch from Sheet2 left join aa on Sheet2.rowid =aa.old_rowid left join cc on Sheet2.rowid=cc.old_rowid;
It sounds like you’ll need a setup where your outgoing pieces are matched to the earliest open shipment until it closes. A running balance usually does the trick subtract from each batch as items go out and once it hits zero you can mark it Closed, if it’s partly used mark it In Progress, and if untouched leave it Open. A mix of formulas like SUMIFS or INDEX/MATCH with maybe a helper column can keep it organized.
Hello Oliver,
I copied the formulas into my file - one worked (Status), one did not ("Out" sequence)
I get a #REF! error when I insert the following formula:
=IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))
I can't seem to attach a file so here is a snip, would you mind taking a look?
Again, thank you for your help!
CHristine
Hello Christine,
you are welcome. Please try this formula that is in cell E7 and filled down in my sample sheet. I have to enter the formula as an arrayformula with ctrl+shift+enter because i work with the download version of Excel 2013.
=IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$1:D1)),1)))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
=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.
Hello Oliver,
I copied the formulas into my file - one worked (Status), one did not ("Out" sequence)
I get a #REF! error when I insert the following formula:
=IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))
I can't seem to attach a file so here is a snip, would you mind taking a look?
Again, thank you for your help!
CHristine
Hello Oliver,
Thank you very much for your help.
I have inserted both formulas into my file, The "Status" formula works, the "Out" sequence does not.
I get a #REF! error when I insert the formula:
IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$6:D6)),1)))
I can't seem to attach a my file so here id a "Snip" of my file.
Would you mind taking a look ? (Column "E")
Again, thank you for your help.
Christine
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.
