PO wise supply issue

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164
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:

Dec.NoDatePO NoHospital NameItemDescriptionPO QtySupply QtyBalancePO Status
KCN-00101/11/201895255NICVDRINTResolute15001001400PO Partial
KCN-00202/11/201862500TABBAINTBaloon40002003800PO Partial
KCN-00304/11/201895255TABBAINTBaloon40004003400PO Partial
KCN-00405/11/201895255NICVDRINTResolute15002001200PO Partial
KCN-00505/11/201895255TABBAINTSurgery40005002900PO Partial
KCN-00606/11/201895255NICVDRINTResolute1500900300PO Partial
KCN-00707/11/201895255TABBAINTBaloon400010001900PO Partial
KCN-00809/11/201895255NICVDRINTResolute15003000PO complete
KCN-00910/11/201895255TABBAINTBaloon40002000900PO Partial
KCN-01010/11/201895255TABBAINTBaloon40009000PO complete

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Small Summary detail PO hospital:

Hospital POH.NameDescriptionDescriptionPO QTY
95255NICVDRINTResulute1500
62500TABBAONYXResulute2000
70001NICVDNCEUPBaloon5000
8001AKUSPLBaloon4000
95255TABBAINTBaloon4000

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>
 
Upvote 0
.
If I correctly understand your issue, you are seeking a means to assign PO numbers that were issued by two different hospitals .. so you can accurately fulfill their order requests.

If this is the issue, consider concatenating the hospital's first letter in their name to the end of the PO number. This will make it easier to sort the data and ensure you are selecting the
right PO numbers for the specific hospital.

Paste this macro into a module. Run it by pasting a command button on the sheet in question. The macro is set for "Sheet1". If your sheet name is different you will need to edit the code
to reflect the correct sheet name.

Code:
Option Explicit




Sub Concat()
    Dim i As Long, lRow As Long
    With Sheets("Sheet1")
        lRow = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 2 To lRow
            Sheets("Sheet1").Cells(i, 3) = .Cells(i, 3) & Left(.Cells(i, 4), 1)
            
        Next i
    End With
End Sub

Download sample workbook : https://www.amazon.com/clouddrive/share/3ZLMjNjWmCjOzc5tQxaU8tiKIAXseWJp7u8RGemjStw
 
Last edited:
Upvote 0
Hi logit:
this is small example actually PO range too lengthy. you can see it summary PO report range N2 to R7:
H.NameHospital POItemDescriptionPO QTY
NICVD95255RINTResulute1500
TABBA62500ONYXResulute2000
NICVD70001NCEUPBaloon5000
AKU8001SPLBaloon4000
TABBA95255INTBaloon4000

<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>

can any you tell me if any error on this, i am using it;
=IF(AND(D3=VLOOKUP(D3,N3:O7,2,0),E3=VLOOKUP(E3,N3:O7,1,0)),CONCATENATE(D3,left(E3,3)),0)
not working in F3:
how i can Qty minus with smallest value of PO, mention below table.

Dec.NoDatePO NoHospital Name ItemDescriptionPO QtySupply QtyBalancePO Status
KCN-00101/11/201895255NICVD#N/ARINTResolute15001001400PO Partial
KCN-00202/11/201862500TABBA62500TABINTBaloon40002003800PO Partial
KCN-00304/11/201895255TABBAFALSEINTBaloon40004003400PO Partial
KCN-00405/11/201895255NICVDFALSERINTResolute15002001200PO Partial
KCN-00505/11/201895255TABBA62500TABINTSurgery40005002900PO Partial
KCN-00606/11/201895255NICVD#VALUE!RINTResolute1500900300PO Partial
KCN-00707/11/201895255TABBA#VALUE!INTBaloon400010001900PO Partial
KCN-00809/11/201895255NICVD#VALUE!RINTResolute15003000PO complete
KCN-00910/11/201895255TABBA#VALUE!INTBaloon40002000900PO Partial
KCN-01010/11/201895255TABBA#VALUE!INTBaloon40009000PO complete

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
hi,
i add a new colume on it smallest value of PO qty apply this
=SMALL(IF((D3=$D$3:$D$12)*(E3=$E$3:$E$12),$J$3:$J$12,""),ROW($A$1:$A$1)) in K3
it works but there is a problem,

balance qty of j3 1400 can be copied in h6 then apply j6=h6-i6 result should be 500. i use it manually 1400-900=500. actullay 500 balance then goto h10 minus 500 supply more then it will become a zero and PO qty zero show every cell. PO status is maintaining manually.

i apply it only one PO:
NICVD and 95255
DatePO NoHospital NameItemDescriptionPO QtySupply QtyBalancesmallest Value POPO Status
01/11/201895255NICVDRINTResolute
1500

<tbody>
</tbody>
10014000PO Partial
02/11/201862500TABBAINTBaloon400020038003000PO Partial
04/11/201862500TABBAINTBaloon4000100030003000PO Partial
05/11/201895255NICVDRINTResolute15009005000PO Partial
05/11/201870001NICVDNCEUPSurgery5000200030003000PO Partial
06/11/201895255TABBAINTResolute4000300010001000PO Partial
07/11/201895255TABBAINTBaloon400050035001000PO Partial
09/11/201895255NICVDRINTResolute150050000PO complete
10/11/201895255TABBAINTBaloon400020038001000PO Partial
10/11/201895255TABBAINTBaloon400020036001000PO partial

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Hi all,
kindly help me to solve this. i am continuous trying.
i use H6:
=LARGE(IF((D6=$D$3:$D$12)*(E6=$E$3:$E$12),J3:J5,""),ROWS($A$1:$A$1))
i got the result Large value matching criteria 1400
but use in H10:
=LARGE(IF((D10=$D$3:$D$12)*(E10=$E$3:$E$12),J3:J9,""),ROWS($A$1:$A$1))
result found again 1400 but i need 400 because j3=h10-i10 result 400.
i use it BUT failed:
=LARGE(IF((D10=$D$3:$D$12)*(E10=$E$3:$E$12),J3:J9,""),ROWS($A$1:$A$1))-small(IF((D10=$D$3:$D$12)*(E10=$E$3:$E$12),J3:J9,""),ROWS($A$1:$A$1)

after this found the smallest value of k3 to k12 match criteria.
where i use
=SMALL(IF((D3=$D$3:$D$12)*(E3=$E$3:$E$12),$J$3:$J$12,""),ROW($A$1:$A$1)) with array and found smallest value of PO
it will be ZERO at J10=h10-i10

Dec.NoDatePO NoHospital NameItemDescriptionPO QtySupply QtyBalancesmallest Value POPO Status
KCN-00101/11/201895255NICVDRINTResolute15001001400400PO Partial
KCN-00202/11/201862500TABBAONYXBaloon 00PO Partial
KCN-00304/11/201862500TABBAONYXBaloon 00PO Partial
KCN-00405/11/201895255NICVDRINTResolute14001000400400PO Partial
KCN-00505/11/201862500TABBAONYXSurgery 00PO Partial
KCN-00606/11/201862500TABBAONYXResolute 00PO Partial
KCN-00707/11/201862500TABBAONYXBaloon 00PO Partial
KCN-00809/11/201895255NICVDONYXResolute14004001000400PO complete
KCN-00910/11/201862500TABBAONYXBaloon 00PO Partial
KCN-01010/11/201862500TABBAONYXBaloon 00PO complete

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Dear James006:
i use your formula mention in carry forword balance.
=INDEX($J$3:$J$12,MATCH(D9&E9,$D$3:$D$12&$E$3:$E$12,0)) with array but not working same balance carry in below.
kindly help me.
Dec.NoDatePO NoHospital NameItemDescriptionPO QtySupply QtyBalancesmallest Value POPO Status
KCN-00101/11/201895255NICVDRINTResolute15001001400400PO Partial
KCN-00202/11/201862500TABBAONYXBaloon40002003800800PO Partial
KCN-00304/11/201862500TABBAONYXBaloon380010002800800PO Partial
KCN-00405/11/201895255NICVDRINTResolute14001000400400PO Partial
KCN-00505/11/201862500TABBAONYXSurgery380020001800800PO Partial
KCN-00606/11/201862500TABBAONYXResolute38003000800800PO Partial
KCN-00707/11/201862500TABBAONYXBaloon38005003300800PO Partial
KCN-00809/11/201895255NICVDONYXResolute14004001000400PO complete
KCN-00910/11/201862500TABBAONYXBaloon38002003600800PO Partial
KCN-01010/11/201862500TABBAONYXBaloon38002003600800PO complete

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top