Forum Discussion
Formula/VBA Help
sherikhan88 Quite some logic but I think I got it. See attached. It's an ugly formula and probably can be optimized.
sherikhan88 Don't think you need to "Excess" columns, by the way. And the logic you want to apply seems to cause trouble. You say that if Store1 doesn't have enough on hand, it should be skipped, with the same logic applied to the following stores.
Suppose the quantity of the first order would be 101, rather than 47, then none of the stores would be selected. Wouldn't it make more sense to say that store 1 delivers 47, store2 50 and store 3 the remaining 4. Or, store 3 delivers all of their 92 items and store 1 delivers 9 more (or any other combination of stores for that matter).
Anyhow, the attached file does what you ask for, unless I completely misunderstood. Note that I changed the formulae in the "Excess" columns to something without IF, hoping you find it useful.
- Hi Riny,
first of all thank you so much to respond to my query and giving your valuable time.
"Wouldn't it make more sense to say that store 1 delivers 47, store2 50 and store 3 the remaining 4" yes this is what i need.
I don't need excess column you are right, I just put it there to see what is the excess quantity in 1 to 6 stores from Column A ( which is required in Store X).
So Store X need certian qty which is mentioned in column A which has to be fulfilled from rest of 6 stores. Issue which i cannot able to solve is, if my fulfilment store will only contribute if they have extra quantity with them ( greater than A column).
Lets Say
1) Store X need 48 qty. My fulfilment store 1,2,3---- has 48,50,100,110 qty, then formula should check what is the qty required by store x and then it started to go to every store and see if its more or less then required qty. then if its less, system will skip that store, if its more then it will metch the max quantity it can fetch to fulfil the requirement but not more then the requirement.
Please check the attached file.sherikhan88 Sorry, but I don't understand the logic of your desired result, but perhaps I misunderstood again. My formulae now look at Store 1 first and fulfills the order with all it has. Then Store 2 fulfills what's left and so on. So for the first order, Store 1 fulfills 47 out of 48 and Store 2 fulfills 1 more. All other stores are zero.
