+ Reply to Thread
Results 1 to 10 of 10

Allocating Stock-In-Hand To Ship-out

  1. #1
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Allocating Stock-In-Hand To Ship-out

    Hi All,

    Please refer to below snapshot. I want your help to be able to allocate available stock as shippable and alongside it running Stock leftover until it gets exhausted.

    So in here I have some dummy order numbers and 3 material in stock and based on the ordered quantity I want to allocate available stock until it becomes zero.

    I tried using nested-if, but since my actual data is too big and some of the cases where for some of the materials the rows are more than 30, I found it difficult to arrive at a formula that suffices my above requirement of allocating shippable and showing stock leftover, if any.

    Thanks for your help in advance.

    Stock.JPG
    Attached Files Attached Files
    Last edited by rahulmamtora; 06-21-2021 at 11:52 AM. Reason: removed merged cells from the excel file

  2. #2
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Allocating Stock-In-Hand To Ship-out

    small example attached - master table only saves the balance movements - one line per item.
    transaction movements are individually recorded - use forms - do not enter data directly into sheet - there are no on-sheet formula involved.
    if however you want an on-sheet formula solution the first thing you must do is - GET RID OF MERGED CELLS.
    torachan.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Re: Allocating Stock-In-Hand To Ship-out

    Thanks Torachan for your reply. Actually, I have huge data from my ERP tool that has various fields, and out of that, I have just shared some key fields that relate to what solution I am looking for.

    And yes, we can get rid of the merged cells (I have updated the attached file, removing the merged cells), as that was just for illustration purposes to show that we have 100, 30, and 80 materials in stock, rather than repeating on each of the rows.

    I would request you and others to please help me with my solution in any which way possible.

    Thanks!

  4. #4
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Re: Allocating Stock-In-Hand To Ship-out

    Bumping this thread for urgent attention. Thanks!

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Allocating Stock-In-Hand To Ship-out

    Rory

  6. #6
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Re: Allocating Stock-In-Hand To Ship-out

    Quote Originally Posted by rorya View Post
    Thanks for pointing out this. Yes, I admit I have also raised my question on another forum, as I waited for 24hrs for a solution on ExcelForums. And now it's become urgent for me to get the solution, hence with "disclaimer" I have posted on another forum. From whichever source I get the exact solution, I will close rest of the threads as solved by giving link to the solution.

  7. #7
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Allocating Stock-In-Hand To Ship-out

    We do not expect anyone to 'bump' a thread within 24 hours (in your case less than 18 hours)
    It may be urgent to you - it is certainly not urgent to me.
    The help we give is voluntary and free of charge, therefore it is given on our term and on our timescale.
    The forum members are from across the globe, they may have been at work or nighttime and asleep, and have not possibly seen your post yet.
    The message is direct and simple, first read the forum rules and abide by them, secondly do not expect us to jump through hoops just to satisfy you.
    torachan.

  8. #8
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Re: Allocating Stock-In-Hand To Ship-out

    Quote Originally Posted by torachan View Post
    We do not expect anyone to 'bump' a thread within 24 hours (in your case less than 18 hours)
    It may be urgent to you - it is certainly not urgent to me.
    The help we give is voluntary and free of charge, therefore it is given on our term and on our timescale.
    The forum members are from across the globe, they may have been at work or nighttime and asleep, and have not possibly seen your post yet.
    The message is direct and simple, first read the forum rules and abide by them, secondly do not expect us to jump through hoops just to satisfy you.
    torachan.
    I respect all your voluntry help which is a noble cause, respect. I agree to what you have said and I put all the efforts to adhere to it. Its just that this time around I have bit of urgency, hence I had to take this step. I hope you understand. Thanks!

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Allocating Stock-In-Hand To Ship-out

    But you still haven't posted all your cross-post links...

  10. #10
    Registered User
    Join Date
    08-05-2005
    Location
    Bangalore
    Posts
    14

    Re: Allocating Stock-In-Hand To Ship-out

    Quote Originally Posted by rorya View Post
    But you still haven't posted all your cross-post links...
    Since you asked for it... here are the reposts

    https://www.ozgrid.com/forum/index.p...t/#post1250524
    https://chandoo.org/forum/threads/al...hip-out.46493/

    I hope this helps.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Allocating stock to racking bays.
    By PasqualeC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-07-2021, 03:19 AM
  2. Sending out data (stock on hand) to clients
    By Landi in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-14-2019, 04:58 PM
  3. [SOLVED] FORMULA to Allocating Stock
    By sunderlalrwr in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-17-2018, 11:17 PM
  4. [SOLVED] Formula for allocating stock to a given requirement
    By JakeMann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-08-2016, 12:46 PM
  5. [SOLVED] Stock list using Vlookup, need the price field to read as TBA is stock on hand is 0
    By meerabell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-31-2015, 07:42 AM
  6. Running Total for Stock On Hand
    By roamanzambia in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-02-2015, 06:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1