Hi,

Pls go thru my file, with reference to other post (https://techcommunity.microsoft.com/t5/excel/to-allocate-stock-from-closing-stock-by-formula/m-p/510624) I made my sheet but I need to add few more logics, I had to add extra column of Available Credit Limit. First formula in allocation qty column will check whether the customer has credit limit or not, if available then it will check the most earliest contract No as per contract date, in same contract number there may be multiple items, first it will check the lowest price of item & its required qty & available qty, if all covers then only the max covered qty will be allocated which will cover the credit limit. In case credit limit is crossing then the max last qty ( fraction will be ignored) which will cover under credit limit that will be allocated. In this way allocation will be continued. I mean in case of multiple items in one single contract number, formula will check first all these logics, first lowest price item then gradually it will search nest higher price item, & will be continued. In my file i already did custom sort in this way:

If required you can suggest me any other best way out for my desired result. Pls mind that Available credit limit will be considered only greater than zero only.

Apart from that there another column will be added ( That I yet to add) for manual allocation, in case of special cases manual allocation also will be considered, & in that case pls suggest me how that can be done, if I add qty manually after a certain time a formula in any other column will restrict me to allocated qty when it will cross available credit limit.

It will be an immense help to get the solution for these

Thanks in advance

Regards

​Hi,Pls go thru my file, with reference to other post (https://techcommunity.microsoft.com/t5/excel/to-allocate-stock-from-closing-stock-by-formula/m-p/510624) I made my sheet but I need to add few more logics, I had to add extra column of Available Credit Limit. First formula in allocation qty column will check whether the customer has credit limit or not, if available then it will check the most earliest contract No as per contract date, in same contract number there may be multiple items, first it will check the lowest price of item & its required qty & available qty, if all covers then only the max covered qty will be allocated which will cover the credit limit. In case credit limit is crossing then the max last qty ( fraction will be ignored) which will cover under credit limit that will be allocated. In this way allocation will be continued. I mean in case of multiple items in one single contract number, formula will check first all these logics, first lowest price item then gradually it will search nest higher price item, & will be continued. In my file i already did custom sort in this way: If required you can suggest me any other best way out for my desired result. Pls mind that Available credit limit will be considered only greater than zero only.Apart from that there another column will be added ( That I yet to add) for manual allocation, in case of special cases manual allocation also will be considered, & in that case pls suggest me how that can be done, if I add qty manually after a certain time a formula in any other column will restrict me to allocated qty when it will cross available credit limit.It will be an immense help to get the solution for theseThanks in advanceRegards  Read More

​