Apr 30 2019 02:30 AM
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)
Apr 30 2019 04:00 AM
Apr 30 2019 04:02 AM
Apr 30 2019 04:04 AM
The file with my suggested formula is attached hereto.
Apr 30 2019 04:16 AM
Apr 30 2019 08:25 PM
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.
May 01 2019 08:20 PM
May 01 2019 10:13 PM
May 01 2019 10:20 PM
@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
May 01 2019 10:50 PM
SolutionIn 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.
May 01 2019 11:20 PM
May 02 2019 04:14 AM
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
May 02 2019 05:11 AM
May 01 2019 10:50 PM
SolutionIn 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.