SOLVED
Home

Need to allocate stock

%3CLINGO-SUB%20id%3D%22lingo-sub-502959%22%20slang%3D%22en-US%22%3ENeed%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-502959%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehere%20attached%20my%20file%20which%20i%20need%20to%20allocate%20my%20stock.%3C%2FP%3E%3CP%3Ein%201st%20table%20i%20have%20order%20number%20%2F%20delivery%20date%20%2F%20items%20and%20required%20qty%20.%202nd%20table%20i%20have%20mentioned%20closing%20stock%20.%20i%20need%20to%20allocate%20that%20closing%20stock%20to%201st%20table%20items%20on%20available%20qty%20column%20based%20on%20delivery%20date%20(%20with%20delivery%20date%20priority-%201st%20qty%20for%201st%20delivery)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-502959%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-503301%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-503301%22%20slang%3D%22en-US%22%3ETry%20this%20formula%20in%20E4%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3D(LOOKUP(C4%2CK%244%3AL%248)%26gt%3B%3D%3CBR%20%2F%3E(SUMIFS(D%244%3AD%2463%2CB%244%3AB%2463%2C%22%26lt%3B%22%26amp%3BB4%2CC%244%3AC%2463%2CC4)%2B%3CBR%20%2F%3ESUMIFS(E%243%3AE3%2CB%244%3AB4%2CB4%2CC%244%3AC4%2CC4)))*D4%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-503303%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-503303%22%20slang%3D%22en-US%22%3Ehi%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%20for%20your%20respond.%20con%20you%20please%20add%20this%20formula%20to%20the%20attachment%20and%20send%20with%20attachment%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-503312%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-503312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20with%20my%20suggested%20formula%20is%20attached%20hereto.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-503344%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-503344%22%20slang%3D%22en-US%22%3Ehi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%3CBR%20%2F%3Eits%20working%2080%25.%20but%20there%20are%20some%20issues.%20as%20a%20example%20when%20we%20filter%20product%20A%20%2C%20after%20your%20formula%20its%20allocated%2038450.%20but%20only%20we%20have%2035700.%20product%202%20and%205%20also%20the%20same.%20product%203%20not%20allocated%20total%20qty.%20product%204%20also%20the%20same.%20can%20you%20please%20help%20for%20this%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506825%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20modified%20the%20formula%20in%20E4%2C%20copied%20down%20rows%2C%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(LOOKUP(C4%2CK%244%3AL%248)%26gt%3B%3D%3CBR%20%2F%3E(SUMIFS(D%244%3AD%2463%2CB%244%3AB%2463%2C%22%26lt%3B%22%26amp%3BB4%2CC%244%3AC%2463%2CC4)%2B%3CBR%20%2F%3ESUMIFS(D%244%3AD4%2CB%244%3AB4%2CB4%2CC%244%3AC4%2CC4)))*D4%3C%2FP%3E%3CP%3ENote%20that%20the%20formula%20returns%200%2C%20if%20the%20closing%20stock%20is%20insufficient%20to%20fulfill%20the%20delivery%20of%20the%20required%20quantity%20of%20products%20on%20a%20given%20date.%20Stated%20differently%2C%20the%20cumulative%20quantity%20of%20the%20product%20as%20at%20a%20given%20date%20cannot%20exceed%20the%20closing%20stock%20of%20such%20product.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510403%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510403%22%20slang%3D%22en-US%22%3Ethank%20you%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20its%20working%20calm.%20is%20there%20any%20way%20to%20allocate%20that%20balance%20stock%20for%20next%20delivery.%20i%20mean%20which%20shows%20as%20%220%22%20but%20can%20add%20some%20qty%20for%20next%20delivery%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510618%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510618%22%20slang%3D%22en-US%22%3EThe%20available%20quantity%20remains%200%20until%20such%20time%20that%20the%20quantity%20of%20closing%20stock%20becomes%20sufficient%20to%20fulfill%20the%20required%20quantity%20of%20the%20product%20on%20a%20given%20date.%20This%20is%20in%20accordance%20with%20the%20law%20that%20entitles%20the%20buyer%20to%20rescind%20the%20sales%20contract%20if%20the%20quantity%20delivered%20is%20less%20than%20the%20quantity%20ordered.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510632%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510632%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20hi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei've%20add%20my%20original%20details%20for%20your%20updated%20chart.%20but%20there%20are%20some%20issues%2C%20can%20you%20please%20look.%20some%20items%20not%20in%20my%20clossing%20stock.%20but%20it%20shows%20as%20balance%20stock%20and%20some%20not%20allocated.%20please%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510681%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%2C%20I%20modified%20the%20formula%20in%20E4%2C%20copied%20down%20rows%2C%20to%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3D(IFNA(VLOOKUP(C4%2CK%244%3AL%241749%2C2%2C0)%2C0)%26gt%3B%3D%3CBR%20%2F%3E(SUMIFS(D%244%3AD%24822%2CB%244%3AB%24822%2C%22%26lt%3B%22%26amp%3BB4%2CC%244%3AC%24822%2CC4)%2B%3CBR%20%2F%3ESUMIFS(D%244%3AD4%2CB%244%3AB4%2CB4%2CC%244%3AC4%2CC4)))*D4%3C%2FP%3E%3CP%3ENote%20that%20the%20foregoing%20formula%20also%20returns%200%20for%20ordered%20items%20without%20available%20quantity%20in%20the%20closing%20stock.%20In%20the%20previous%20version%2C%20it%20was%20assumed%20that%20all%20ordered%20items%20are%20included%20in%20the%20closing%20stock%20but%20the%20quantities%20thereof%20may%20be%20insufficient%20to%20fulfill%20that%20quantity%20to%20be%20delivered%20on%20a%20given%20date.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510718%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510718%22%20slang%3D%22en-US%22%3Eevery%20thing%20sorted.%20thank%20you%20very%20much%20for%20your%20support.%20appreciate%20that%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510749%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510749%22%20slang%3D%22en-US%22%3EYou're%20welcome.%20I'm%20glad%20we%20finally%20did%20it.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511423%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511423%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHI%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20have%20one%20more%20question.%20i%20added%20new%20column%20and%20give%20a%20condition%20by%20IF%20formula%20to%20get%20complete%20items%20and%20non%20complete%20items.%20and%20also%20i%20have%20added%20new%20sheet%20and%20copy%20closing%20stock%20details%20for%20it.%20i%20need%20to%20make%20a%20hyperlink%20when%20click%20on%20condition%20column%20cell%20to%20filter%20relevant%20data%20from%20sheet%202%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511713%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20to%20allocate%20stock%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511713%22%20slang%3D%22en-US%22%3EBecause%20you%20are%20now%20raising%20a%20new%20issue%2C%20I%20suggest%20you%20post%20it%20in%20a%20new%20conversation.%20Thereby%2C%20you%20will%20be%20able%20to%20obtain%20more%20suggestions%2C%20not%20necessarily%20from%20me.%3C%2FLINGO-BODY%3E
Ravindu94
Contributor

Hi,

 

here attached my file which i need to allocate my stock.

in 1st table i have order number / delivery date / items and required qty . 2nd table i have mentioned closing stock . i need to allocate that closing stock to 1st table items on available qty column based on delivery date ( with delivery date priority- 1st qty for 1st delivery)

13 Replies
Try this formula in E4, copied down rows:
=(LOOKUP(C4,K$4:L$8)>=
(SUMIFS(D$4:D$63,B$4:B$63,"<"&B4,C$4:C$63,C4)+
SUMIFS(E$3:E3,B$4:B4,B4,C$4:C4,C4)))*D4
hi,

thanks for your respond. con you please add this formula to the attachment and send with attachment

@Ravindu94 

The file with my suggested formula is attached hereto. 

hi @Twifoo
its working 80%. but there are some issues. as a example when we filter product A , after your formula its allocated 38450. but only we have 35700. product 2 and 5 also the same. product 3 not allocated total qty. product 4 also the same. can you please help for this

@Ravindu94 

In the attached file, I modified the formula in E4, copied down rows, to this: 

=(LOOKUP(C4,K$4:L$8)>=
(SUMIFS(D$4:D$63,B$4:B$63,"<"&B4,C$4:C$63,C4)+
SUMIFS(D$4:D4,B$4:B4,B4,C$4:C4,C4)))*D4

Note that the formula returns 0, if the closing stock is insufficient to fulfill the delivery of the required quantity of products on a given date. Stated differently, the cumulative quantity of the product as at a given date cannot exceed the closing stock of such product. 

thank you @Twifoo its working calm. is there any way to allocate that balance stock for next delivery. i mean which shows as "0" but can add some qty for next delivery
The available quantity remains 0 until such time that the quantity of closing stock becomes sufficient to fulfill the required quantity of the product on a given date. This is in accordance with the law that entitles the buyer to rescind the sales contract if the quantity delivered is less than the quantity ordered.

@Twifoo  hi,

 

i've add my original details for your updated chart. but there are some issues, can you please look. some items not in my clossing stock. but it shows as balance stock and some not allocated. please

Solution

@Ravindu94 

In the attached file, I modified the formula in E4, copied down rows, to this: 

=(IFNA(VLOOKUP(C4,K$4:L$1749,2,0),0)>=
(SUMIFS(D$4:D$822,B$4:B$822,"<"&B4,C$4:C$822,C4)+
SUMIFS(D$4:D4,B$4:B4,B4,C$4:C4,C4)))*D4

Note that the foregoing formula also returns 0 for ordered items without available quantity in the closing stock. In the previous version, it was assumed that all ordered items are included in the closing stock but the quantities thereof may be insufficient to fulfill that quantity to be delivered on a given date. 

every thing sorted. thank you very much for your support. appreciate that
You're welcome. I'm glad we finally did it.

 

@Twifoo 

 

HI

 

i have one more question. i added new column and give a condition by IF formula to get complete items and non complete items. and also i have added new sheet and copy closing stock details for it. i need to make a hyperlink when click on condition column cell to filter relevant data from sheet 2

@Twifoo 

Because you are now raising a new issue, I suggest you post it in a new conversation. Thereby, you will be able to obtain more suggestions, not necessarily from me.
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies