Forum Discussion
Excel - Matching outgoing to incoming inventory based a specific conditions
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
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
