i've tried lots of times to get a solution for my problem. but still not sorted that. here attached my sheet with three tables. in first table i have mentioned order number delivery date of that order number / required items order number wise and required qtys. in my 2nd table i have mentioned bill of materials ( 29 to 32 item required for make one model). in my 3rd table i have mentioned items wise closing stock details. most of the items common for every order number. i need to allocate stock to against order. i need to prioritize delivery date. as a example if i have 800 pcs of closing stock in one item.i have 500 wise two deliveries. i need to allocate 1st 500 for first delivery and balance 300 for second delivery.