Hello & Sorry for the confusing title,
I am trying to do following in less steps (preferably one step). I will first explain what I need to be done, then how i am currently doing it.
I need to find out what week/date my "CurrentLevel" will hit (be equal to or below) my "LowestLevel" allowed based on two other tables which represent product coming in and demand for the product (product going out). These two tables (in tabular format) have dates associated with them and will effect when the "CurrentLevel" hits the "LowestLevel".
So, I could have 200 apples coming in but at separate times. 50 coming in next week, 50 coming in three weeks from now, etc. In addition, there is the reverse effect on demand. I could have 10 going this week, 30 going out the following week, etc.
I tried to represent these two tables below with "In" & "Out" columns.
wk range = 1 - 10 but i am working with actual dates that can go into next year etc.
Currently, pivot both to put into a matrix and then use another table and index-matches each in & out matrix to get the difference of what is happening that week for that product. Then I use another table that starts off at my currently level, use and index-match on the difference table and then, finally, I use that table and index-match when it is below the current stock value.
I am hoping there is just one grand formula I can use, or weed out some the step to get this info.
I tried my best to explain it here. If there is any confusion, please let me know.
Thanks!
alexb
Tables are below:
[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]LowestLevel[/TD]
[TD]CurrentLevel[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]peachs[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]In (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Out (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]
I am trying to do following in less steps (preferably one step). I will first explain what I need to be done, then how i am currently doing it.
I need to find out what week/date my "CurrentLevel" will hit (be equal to or below) my "LowestLevel" allowed based on two other tables which represent product coming in and demand for the product (product going out). These two tables (in tabular format) have dates associated with them and will effect when the "CurrentLevel" hits the "LowestLevel".
So, I could have 200 apples coming in but at separate times. 50 coming in next week, 50 coming in three weeks from now, etc. In addition, there is the reverse effect on demand. I could have 10 going this week, 30 going out the following week, etc.
I tried to represent these two tables below with "In" & "Out" columns.
wk range = 1 - 10 but i am working with actual dates that can go into next year etc.
Currently, pivot both to put into a matrix and then use another table and index-matches each in & out matrix to get the difference of what is happening that week for that product. Then I use another table that starts off at my currently level, use and index-match on the difference table and then, finally, I use that table and index-match when it is below the current stock value.
I am hoping there is just one grand formula I can use, or weed out some the step to get this info.
I tried my best to explain it here. If there is any confusion, please let me know.
Thanks!
alexb
Tables are below:
[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]LowestLevel[/TD]
[TD]CurrentLevel[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]122[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]190[/TD]
[/TR]
[TR]
[TD]peachs[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]56[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]In (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Product[/TD]
[TD="width: 64"]Qty[/TD]
[TD="width: 64"]Out (wk #)[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]apples[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]peaches[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]oranges[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]watermelon[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]