I don't quite know how to word this.
I have a set of data which comes in two columns (essentially containers in storage or containers arriving soon)
I then take the two sets of data, and plug it into a "production schedule" using the below formula, only after I have added both unique columns into one workable column (seen in Column "O" or "Combined"). The formula which plots them into 4 workable slots per day is as follows:
=ArrayFormula(split(transpose(split(regexreplace(query(array_constrain(if(mod(row(O5:O), 4) = 2, "^", "|") & O5:O,max(row(O5:O) * (O5:O<> "")),1),,9^99), "^^",), "^", true, false)), "|", true, false))
For some unknown reason which I have yet to resolve, the above formula leaves a couple spaces instead of filling it with potential containers within the first couple of days, which I have yet to resolve.
The issue I have is that the above formula now plots for me each day I can work the containers, but it is not sense checking whether the containers will actually arrive in time for them to be worked in reality according to how the above formula is plotting them to be worked.
MY QUESTION:
How do I add to the above formula so that it also checks to make sure that the date it is automatically plotted in for "production" aligns with a 2 day logic test for the containers arrival. I.E. if the container is to arrive on the 10th September, logically it can only be worked from the 12th September, not on the 10th September or before the 10th September. If this check says it can only be worked later, then it should plot from the day it can be worked and then each container afterwards must be placed in a "production slot".
It also needs to take into consideration what is in storage first before plotting, but I think this is resolved with the unique formula I am using to adjust the plotting.
The sample sheet I am working off of:
https://docs.google.com/spreadsheets/d/1_niDFQtTcL9c3yROeh4qHmD_irBGbF066Pz2jEaQ674/edit?usp=sharing
Hope you can assist me here, this is a mammoth formula I am trying to plot to make a manual job substantially easier going forward.
Thanks in advance.