+ Reply to Thread
Results 1 to 7 of 7

Allocation of products based on order entry and delivery dates

  1. #1
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Allocation of products based on order entry and delivery dates

    Good afternoon all,

    Wondering if anyone has any ideas on how to work an allocation of products based on the Entry date, Ship date product arrival date and inventory received from excel.?

    I can't seem to wrap my head around this and hoping someone could give me a nudge!

    Thanks in advance
    Ian

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,088

    Re: Allocation of products based on order entry and delivery dates

    Way too little information.

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually).

    4. Try to avoid using merged cells. They cause lots of problems!

    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Glenn



  3. #3
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: Allocation of products based on order entry and delivery dates

    Morning All,

    Yes i am aware that an actual sheet would be required to prove the concept would work... At this point i haven't put the time into creating a test sheet as I'm not sure it could be pulled off... But if you believe it can happen i will make up a sheet today and upload.

    Thanks again for your time
    Ian

  4. #4
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: Allocation of products based on order entry and delivery dates

    Please see the attached test sheet.. everything made up but industry is relevant..

    I guess the goal of this allocation sheet would be to identify what orders need to be printed for our warehouse to pick/ship first based on the stock receiving/ETA dates.
    with the order entry date being the ultimate decider, first in first filled kind of rule.. but if the first entered order is not going to ship until after the second (D2) delivery then it can wait for that delivery to arrive so that we can maximize our output and not keep stock on the shelves if it can be shipped to a customer.

    Thanks again for the time spent on this!
    Ian
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,060

    Re: Allocation of products based on order entry and delivery dates

    Perhaps this will be of help.
    Sheet1 is populated using selected columns from the tables on the 'Orders' and 'Incoming Product' sheets.
    Note that originally the data is pasted into Sheet1 from each sheet and then sorted by date (this assumes using the date in column D on the 'Order' sheet).
    Column B is populated using: =IF(ISNUMBER(A4),"Out","In")
    Columns M:S are populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Column T is populated using: =COUNTIFS(M4:S4,">0")=7
    Column U is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    On the 'Orders' sheet the date for which the ticket may be printed is populated using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    08-24-2018
    Location
    Montreal, Canada
    MS-Off Ver
    MS Office 2016
    Posts
    59

    Re: Allocation of products based on order entry and delivery dates

    Thank you JeteMc.

    ian

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,060

    Re: Allocation of products based on order entry and delivery dates

    You're Welcome. Thank You for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. Replies: 10
    Last Post: 09-21-2017, 08:03 PM
  2. [SOLVED] Generate report based on master file dates and delivery notes
    By sharathnarayanan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-28-2017, 12:36 PM
  3. Replies: 8
    Last Post: 07-01-2016, 02:07 AM
  4. Need to find the total count of products order based on color and sizes
    By sudhina in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 10-16-2014, 08:26 AM
  5. Delivery note or delivery order sheet
    By Roy Istanbouli in forum Excel General
    Replies: 1
    Last Post: 01-21-2005, 02:06 PM

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