Sequential Calculations

[Rik76]

Hi,

I'm have a large data set and am trying to identify a formula that will make a calculation based on the sequence number. Example below.

From a lookup I have identified that a quantity of 3 are packed, but I want the 3 to be deducted from the first schedule line only, and if the schedule quantity is less than the packed quantity, to deduct the remainder from the next sequence and so on.

In the case of the below, in the sequence qty column, the results should read: 2 for sequence 1, 1 for sequence 2 and 0 for sequence 3.

Anyone have any ideas? I'm hoping its possible! Thanks in advance to anyone that can help

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 430"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]ORDER No_ITEM No[/TD]
[TD]Total Order Quantity[/TD]
[TD]Sequence Number[/TD]
[TD]Sequence Qty[/TD]
[TD]Packed Qty[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

so 6 are ordered and you have 3 packed, so 3 are outstanding. maybe define what a sequence is for us ? Why is sequence quantity 2 in the first row ?

 

[Rik76]

so 6 are ordered and you have 3 packed, so 3 are outstanding. maybe define what a sequence is for us ? Why is sequence quantity 2 in the first row ?

Thanks for the response.

You are correct, in that 6 are ordered, and 3 are packed for that order leaving 3 outstanding. The sequence is based on material availability, so in essence we are saying sequence 1, there will be two available, sequence 2, there will be one, and three will be available for sequence 3. The cells have been ordered by sequence number, the sequence qty can be anything.

Looking for a way for the packed quantity, to be deducted from the sequenced qty, according to the sequence number ......so total packed qty deducted from sequence 1 first, then 2 (if applicable - i.e. packed qty greater than sequence qty - then sequence 3 (again, if applicable).

 

[oldbrewer]

sequence 1, there will be two available, Why ?

 

[Rik76]

sequence 1, there will be two available, Why ?

Each sequence represents a different delivery quantity.... usually (but not exclusively) on different days. The total of each sequence makes up the total order / line item quantity.... does that make sense? Sorry if I’m not explaining it too week

 

[oldbrewer]

if you have 3 in stock, and 6 are ordered, surely you would dispatch 3 ?

 

[Rik76]

if you have 3 in stock, and 6 are ordered, surely you would dispatch 3 ?

Perhaps I have not made the example clear; apologies.

The sequence numbers reflect different product call off quantities, so these would be packed as soon as the corresponding date is reached. They would not be despatched until collected - EXW delivery terms - so sometimes you can have multiple sequence lines "packed" as they are still awaiting collection.

Each sequence line will have a date assigned - e.g. sequence 1 could be 01.04.2019; sequence 2 could be 01.05.2018 etc..

The quantity packed, currently duplicates across all sequences, as the lookup to the delivery table does not include a reference to the sequence line....so I am looking up 6075_10 and it returns 3 for the first row, then 3 again for the second etc....I need a way to be able to lookup 6075_10, then based on the returned quantity, identify the sequence numbers - can just be one, can sometimes be multiple - then assign the value returned from the lookup, to the sequence lines, in order of sequence.

The original table would then report as follows: Total 3 are identified for 6075_10 using the lookup then:

[TABLE="class: cms_table, width: 430"]
<tbody>[TR]
[TD]ORDER No_ITEM No[/TD]
[TD]Total Order Quantity[/TD]
[TD]Sequence Number[/TD]
[TD]Sequence Qty[/TD]
[TD]Packed Qty[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]6075_10[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

sorry - I still do not understand - maybe sequence 1 could be 2 items as soon as 2 items are available, even if 20 were available ?

 

[Rik76]

sorry - I still do not understand - maybe sequence 1 could be 2 items as soon as 2 items are available, even if 20 were available ?

The quantities are set according to the customer requirements - if they order 6 units, they may wish to take all 6 on one shipment, or may wish to stagger the delivery of the 6 units in quantities according to their needs, so in this instance to fulfil the total requirement ordered, we are asked packing to the following schedule:

Sequence 1 - Pack Qty 2 on 01.03.2019
Sequence 2 - Pack Qty 1 on 02.04.2019
Sequence 3 - Pack Qty 3 on 03.05.2019

The quantities are customer need / order requirements, not stocked requirements.

 

[oldbrewer]

so packed quantity is really "stock level" and sequence is really "order quantity"

so as you produce more stock, stock level rises, and as you dispatch orders stock level diminishes. Planned stock dispatch is scheduled according to customer requirements and is either dispatched on time or late ??? Not being pedantic, but still struggling to understand the issue !