PO wise supply issue

[majidsiddique]

Hi all,
I have a problem in issue the products on the basis of PO wise ( PO means purchased order issue by Hospital with all term and condition QTY, PRICE ,Supply, etc. ). We can’t serve complete PO qty on a one day with different reason but issue to hospital on demand basis. There is a problem in PO No because same PO No issued by another hospital with different or same item description.
In this way I make a small summary of PO wise detal issue by hospital:
In H3 used formula to find out value of PO Qty issued by Hospital:
=IF(AND(D3=$M$3:$M$7,E3=$N$3:$N$7),VLOOKUP(E3,$N$3:$P$7,3,0),0)
In H7 to H9 formula failed:
H7=0, H8=#value!, H9= #value! Result
In j3:
I have to balance out of PO Last balance Qty – issued Qty automatic like below mention table.
I find out out small PO balance Qty in a table but, how to apply with minus issued Qty.
P9={=SMALL(IF((n9=D3:D12)*(o9=E3:E12),J3:J12,""),ROWS(A1:A1))}
When PO balance Qty of any PO no ( 95255 and NICVD ) will be Zero then all PO partial would be changed PO complete where they are.
If anybody has a better idea to record this transaction in a different way to easy find out my result then tell me.
It is manual sheet update with small data:

[TABLE="width: 708"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Dec.No[/TD]
[TD]Date[/TD]
[TD]PO No[/TD]
[TD]Hospital Name[/TD]
[TD]Item[/TD]
[TD]Description[/TD]
[TD]PO Qty[/TD]
[TD]Supply Qty[/TD]
[TD]Balance[/TD]
[TD]PO Status[/TD]
[/TR]
[TR]
[TD]KCN-001[/TD]
[TD]01/11/2018[/TD]
[TD]95255[/TD]
[TD]NICVD[/TD]
[TD]RINT[/TD]
[TD]Resolute[/TD]
[TD]1500[/TD]
[TD]100[/TD]
[TD]1400[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-002[/TD]
[TD]02/11/2018[/TD]
[TD]62500[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Baloon[/TD]
[TD]4000[/TD]
[TD]200[/TD]
[TD]3800[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-003[/TD]
[TD]04/11/2018[/TD]
[TD]95255[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Baloon[/TD]
[TD]4000[/TD]
[TD]400[/TD]
[TD]3400[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-004[/TD]
[TD]05/11/2018[/TD]
[TD]95255[/TD]
[TD]NICVD[/TD]
[TD]RINT[/TD]
[TD]Resolute[/TD]
[TD]1500[/TD]
[TD]200[/TD]
[TD]1200[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-005[/TD]
[TD]05/11/2018[/TD]
[TD]95255[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Surgery[/TD]
[TD]4000[/TD]
[TD]500[/TD]
[TD]2900[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-006[/TD]
[TD]06/11/2018[/TD]
[TD]95255[/TD]
[TD]NICVD[/TD]
[TD]RINT[/TD]
[TD]Resolute[/TD]
[TD]1500[/TD]
[TD]900[/TD]
[TD]300[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-007[/TD]
[TD]07/11/2018[/TD]
[TD]95255[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Baloon[/TD]
[TD]4000[/TD]
[TD]1000[/TD]
[TD]1900[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-008[/TD]
[TD]09/11/2018[/TD]
[TD]95255[/TD]
[TD]NICVD[/TD]
[TD]RINT[/TD]
[TD]Resolute[/TD]
[TD]1500[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]PO complete[/TD]
[/TR]
[TR]
[TD]KCN-009[/TD]
[TD]10/11/2018[/TD]
[TD]95255[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Baloon[/TD]
[TD]4000[/TD]
[TD]2000[/TD]
[TD]900[/TD]
[TD]PO Partial[/TD]
[/TR]
[TR]
[TD]KCN-010[/TD]
[TD]10/11/2018[/TD]
[TD]95255[/TD]
[TD]TABBA[/TD]
[TD]INT[/TD]
[TD]Baloon[/TD]
[TD]4000[/TD]
[TD]900[/TD]
[TD]0[/TD]
[TD]PO complete
[/TD]
[/TR]
</tbody>[/TABLE]

 

[majidsiddique]

hi,
give any idea to record this transaction another way to find out the solution.
thankful to your comments

 

[majidsiddique]

hi all,
please help me in this way. i try to record this trisection in this way.
below table shows qty issue against PO wise.
is it possible to replace value of C1 with H2:
H2=C1-G2
every last transaction of matching PO No replace it.
H6 = 4700 but actually result 4000.
H6= 4600-300

please reply comment or idea.

[TABLE="width: 605"]
<colgroup><col span="4"><col><col span="2"><col></colgroup><tbody>[TR]
[TD]PO [/TD]
[TD]1121[/TD]
[TD]5000[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]PO #[/TD]
[TD]Qty issue Ag PO[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]PO [/TD]
[TD]222[/TD]
[TD]10000[/TD]
[TD][/TD]
[TD]01/12/2018[/TD]
[TD]1121[/TD]
[TD]400[/TD]
[TD]4600[/TD]
[/TR]
[TR]
[TD]PO [/TD]
[TD]333[/TD]
[TD]4000[/TD]
[TD][/TD]
[TD]02/12/2018[/TD]
[TD]222[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]03/12/2018[/TD]
[TD]222[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]04/12/2018[/TD]
[TD]222[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]05/12/2018[/TD]
[TD]1121[/TD]
[TD]300[/TD]
[TD]4700[/TD]
[/TR]
</tbody>[/TABLE]