Dear Friends,
I am trying to allocate excess quantities of different branches between them but facing some issues i.e
once I allocate the quantity I cannot calculate remaining after allocate each,
secondly i need to improve allocation formula in action column so that if remaining quantity of any item is less than quantity needed the formula should allocate remaining quantity,
for understanding i have upload excel sample file with formulas that i am using,
Thanks in advance,
imran.
please your help
i am not received any reply
I looked that this a couple of days ago. I had no idea what you wanted, no idea what the underlying logic was. I have looked at it again. I still haven't a clue what you want. You need to try to explain in more detail. What do you expect to see as your result (calculate manually) and where do you want to see it?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I think I know what you want. I have made extensive changes to the sheet. See how close this comes....
Dear Friend Glenn Kennedy,
Thanks for your reply,
I am sure you understood well about my requirement,
Actually I have overstock of some items @ stores and at the same time other stores need these stock that's why I have to make inter store transfer plan so that I can liquefy these overstocks, I was doing this as per first attachment but I cannot allocate, liquefy and calculate the balance if this is less than required of any store,(as you Partial allocated in your changed sheet)
I have understand your extensive changed file and this is wonderful but I need to change something in that is opening should be in separate column and demand or required quantity should be in separate so that these things will not be mixed with each other and I can understand well about this, so opening stock (that is excess at stores) should be in separate and balance should be calculated separated as this is already in separate column,
Kindly let me know if you need any further details,
Thanks in advance,
imran.
Originally Posted by Imran368
I am waiting your reply friend
Sorry, I missed your reply. I do not understand your explanation. Can you SHOW me what you want on an amended version of my sheet?
Sorry Brother,
I also missed your message because of much busy and cannot check my mail.
kindly see in attached file about my last requirement that I have tries to explain by text,actually opening stock will be changed for every store so I need this in separate column.
kindly let me know if there is any further details,
Thanks in advance,
Try this.
Column E (yellow) is not needed, is confusing (in my view) and can be deleted.
Columns G & H are not needed, but may be useful for info per sku per store, but may be deleted.
If this isn't right, please indicate which results are incorrect, what they should be and (most importantly) why....
Dear Friend,
Hope you are in good mood,
yellow column E is cumulative of allocated quantity that i was using in past, it is indicating that how much you have allocated if you want to remove no issue,
secondly as i assumed that you are thinking that incoming stock is line wise mean 6,6,6,6, for sku code 1000058 but actually this is only 6 but repeating because of same sku code, so once you allocate 3 for first store 3 pieces your balance is 3 and second store need 4 pieces for same sku code but balance is 3 so it should be partial allocated 3 as it is mentioned in shortage sheet in same excel file of your previous amendment formula,
now in first sku (1000058) you are exceeding to allocate more than 6 that is not accurate,
it should be 3 for hkp06 store and remaining 3 for hhp20 which is actually need 4 but partially allocated 3,
your solution on shortage sheet in sample file is accurate but i just want to make opening and needed skus in different column so that it will not repeat.
actually this is small testing data but i have a bundle of data where i have more than 100,000 skus, so i did not want to place in same column and slow down the performance of excel.
kindly let me know if need any further details,
thanks for your understanding
Last edited by Imran368; 09-10-2019 at 12:39 AM. Reason: sample file edited
Waiting
Last edited by Glenn Kennedy; 09-10-2019 at 08:17 AM. Reason: Clutter removed.
Show me how the next delivery of 1000058 is received, to top up your CENTRAL stock? I have removed all those surplus 6s. They are totally confusing.
Dear Friend Glenn,
Sorry for late reply,
I have mentioned everything in attached excel file as your requirement,
kindly check and confirm if there is any further details,
repeating of skus is because of different stores need different quantities and this is from excess stock sheet from where we need to distribute.
if you want to manage this from different ways you can,i have shown required results that will give idea to remove confusion,
Thanks in advance,
imran.
Still waiting
You will continue to wait!! After 13 posts, I really can't understand what you want, so I'm out... Sorry!!!
Brother
I am feeling sorry because i cannot make you understand
Anyhow thanks for your nice support,
imran.
I am feeling really sorry that i cannot make you understand
Dear Glenn
Thanks for your usual support,
I have utilize your last provided formula but only confusion in one column only (Allocated) where result is not showing accurate,
kindly your help to review this so that It can show result as I have mentioned below in sample file,
Thanks in advance
imran
Dear Glenn,
I have solved this also by changing some formula as mentioned below,
thanks for your nice support because this is happen with your help,
imran.
=IFERROR(MIN(D2,LOOKUP(2,1/($A$1:A1=A2),$G$1:G1)),D2)
Good day i need this sheet please
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks