cell value disruption

[aqeelnokia99]

[TABLE="width: 332"]
<tbody>[TR]
[TD="align: center"]PO[/TD]
[TD="align: center"]qty[/TD]
[TD="align: center"]avaible qty[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]c[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

===========================
Result i want
[TABLE="width: 275"]
<tbody>[TR]
[TD="align: center"]PO[/TD]
[TD="align: center"]qty[/TD]
[TD="align: center"]avaible qty[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]a[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]b[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]c[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]d[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[dave2018]

This is for sure not the best way, but it works:

Drag E2 down...

Excel 2016 (Windows) 32 bit
	A	B	C	D
1	PO	qty	avaible qty	Result
2	a	2	4	2
3	a	3		2
4	b	4	7	4
5	b	3		3
6	b	5		0
7	c	3	3	3
8	c	2		0
9	d	1	12	1
10	d	5		5
11	d	6		6
12	d	7		0
Sheet4

Cell Formulas
Range	Formula
D2	=IF(SUM(B2,D1)<=INDEX($C$2:$C$12,MATCH(A2,$A$2:$A$12,0)),B2,IF(INDEX($C$2:$C$12,MATCH(A2,$A$2:$A$12,0))-SUMPRODUCT(($A1:A$2=A2)*$D1:D$2)$C$2:$C$12,MATCH(A2,$A$2:$A$12,0))-SUMPRODUCT(($A1:A$2=A2)*$D1:D$2)))

 

[aqeelnokia99]

awsome

 

[dave2018]

my pleasure.

 

[Eric W]

Here's a different approach that's a bit shorter:

D2: =IF(C2<>"",MIN(B2,C2),MIN(B2,LOOKUP(9E+307,C$2:C2)-SUM(INDEX(D$1:D1,LOOKUP(9E+307,C$1:C1,ROW(C$1:C1))):D1)))

 

[Eric W]

And even shorter, relying on the values in column A:

D2: =MIN(B2,SUMIF(A:A,A2,C:C)-SUMIF(A$1:A1,A2,D$1:D1))

 

[aqeelnokia99]

u are awsome

 

[aqeelnokia99]

D2: =MIN(B2,SUMIF(A:A,A2,C:C)-SUMIF(A$1:A1,A2,D$1:D1))


this is too good can any one make video about this formula how its work

 

[Eric W]

Well, I don't know if it needs a video!

Here's how it works though. The first SUMIF sums up everything in column C matching the PO in column A. Since according to your layout, you only show the available quantity in one place for each PO, this will return the starting available quantity for the PO, no matter which row of the PO you're looking at.

The next SUMIF adds up all the used amounts for the PO above the current line. See how the ranges have an absolute reference indicator $ for the top row, but not the bottom row. So as you drag the formula down, the range grows. Since I started 1 row above the current row, the SUMIFS will sum everything we've used so far.

Then when you take the difference of the 2 SUMIFS, you have the remaining amount for the PO.

Finally, let's say column B has a quantity of 5, and you have a remaining amount of 7. If you take the minimum of 5 and 7, you get 5, which is what the result is. On the next row, the 5 would be added to the used amount, so the remaining amount would change to 2. Let's say on that row column B has a quantity of 6, and there's a remaining amount of 2. Again, we take the minimum of 6 and 2, get 2 and that's the result. On the next row, 2 would be added to the used amount, so the remaining amount would become 0. You kind of have to work through the cases before you can really see how it works, but it's actually pretty easy. You just need to look at things from a different angle, which is why it took 2 attempts for me to get to it.

I'm glad I could help.