Nov 18 2021 01:40 AM
Nov 18 2021 01:40 AM
Please assist me in below situation
In A column, I have a Quantity which are required to fill "X" store, but this should be fetch from other Stores lets say 1,2,3,4,5,6
Condition is if my store #1 has less qty than what I need in A column for store X, then it should return zero (should not give anything), and run the logic in next column with different store.
But if Quantity in Store 1 is greater than what is required for Store X, then it should check the difference and assigned the quantities which is required by X store (nothing more).
IF store x need 50 qty but store 1 only able to fulfil 40 then for remaining 10 quantities it will check in next store and so on.
Note: if store 1 has 151 quantity and can fulfil required 50 quantity for X store then it will only give 50 not 51, and when formula move to anotehr colum then it should come as 0, cause required quantity is already fulfilled by previous stores.
Nov 18 2021 07:33 AM
@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.
Nov 23 2021 03:27 AM
Nov 23 2021 04:09 AM
@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.
Nov 23 2021 04:58 AM
@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.
Nov 23 2021 07:14 AM
@Riny_van_Eekelen You're Awesome!!! Not only you understand and solved my issue. But gave your valuable time to me.
I kept thinking about the solution for entire day, when i posted it but unable to put the logic into formulas.
You will be remembered by rest of my generation ! Thank you for all you help.