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.
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.
Riny_van_Eekelen Thanks again.
I work in retial, we have item codes which needs to be allocated from warehouse to store. Lets say there are few item codes for which stock in warehouse is zero, and one of the store is selling those items alot due to which it runs out of stock, this is my X store and after doing some calculation my system tells that hey sheri, you X store need 47 qty for one week.
Now, I do not have that item available in warehouse so the option is to fetch the stock from other store.
But other store also need some stock to sell (Minimum Display Quantity-MDQ), here in Column A (which is also the qty required by Store X) we mentioned the MDQ for all the remaining store.
I do not want to fecth anything extra from any store which is not meeting MDQ, and I also do not want to fetch anything extra if the quantity is fulfil.in my first example, My MDQ/or Store X requirement is 48.
My store 1 has 47 Qty available, since store 1 stock is less than 48, I do not want to consider this stock for donation.
Now I will check my second store, which has 50 qty, means 2 item extra. So system should take 2 items now the remaining qty required is (48-2=46), then i will see if my third store can fulfil it.
My third store has 92 qty= 92 is greater than 48, =92-48= 44. Store 3 can give 44 and i need 46, so we will take 44 from store 3, remaining quantity is =48-(2+44)= 2, so i will check store 4
My store 4 has 100 qty, and its greater than 48, so it can fulfil my requirement, but now i just need 2 qty. though it can give me 52. So here i want that i will only get 2 qtyamd when I go for next store, no matter how much qty it has it will say 0.
sherikhan88 Quite some logic but I think I got it. See attached. It's an ugly formula and probably can be optimized.