Recalculate part quantity based on part level to remove parts used in zero quantity assebly

[AndrewTucker]

Need to recalculate total part quantity based on :

• if part quantity is zero, then result qty will be zero (this is simple just an if statement)
• if part report up to an zero qty part e.g. if part level is "8" and it's preceding part "7" is zero qty; then part with level "8" is also zero in quantity.
• resetting the part qty if the new part level is less than the zero Qty level

[TABLE="class: grid, width: 410"]
<tbody>[TR]
[TD="align: center"]Part Level
[/TD]
[TD="align: center"]Part Type
[/TD]
[TD="align: center"]Qty
[/TD]
[TD="align: center"]Result
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Part 1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Part 2
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Part 3
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Part 4
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Part 5
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Part 6
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Part 7
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 8
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 9
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Part 10
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD]These report to zero qty part; thus actual shipped qty is zero
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]Part 11
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]Part 12
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]Part 13
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]Part 14
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]Part 15
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]Part 16
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]0
[/TD]
[TD]These should be zero also
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]Part 17
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]Part 21
[/TD]
[TD="align: center"]7
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]Part 22
[/TD]
[TD="align: center"]8
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Part 16
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD]Part level resets here
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 19
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 20
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Part 21
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Part 22
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 23
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 24
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 25
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 26
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 27
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 28
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 29
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 30
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 31
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 32
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 33
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 34
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 35
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Part 36
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"]0
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Part 37
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]0
[/TD]
[TD]Reports to zero qty
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Part 38
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]1
[/TD]
[TD]Part level resets here
[/TD]
[/TR]
</tbody>[/TABLE]

I am using formula in result column (column D): =IF(C2=0,0,IF(AND(A2>A1,D1=0),D1,C2)
But this formula resets the result value if the part level is less than previous row but is greater than the "Zero quantity" part level. e.g. red highlighted line in above table.

Pls help. Thanks a lot in advance.

 

[igold]

Cross Post:

excel - Recalculate part quantity based on part level to remove parts used in zero quantity assebly - Stack Overflow