+ Reply to Thread
Results 1 to 8 of 8

Find the Next delivery date for a certain customer

  1. #1
    Forum Contributor MariaPap's Avatar
    Join Date
    10-19-2013
    Location
    Chania-Crete-Greece
    MS-Off Ver
    Excel 2003-2010
    Posts
    319

    Find the Next delivery date for a certain customer

    Hi

    I have a list with customers and the dates that we send goods to them.

    My goal is to find(knowing that today is Wednesday), which is the next delivery date for a certain customer.

    Any idea for that?

    Thanks in advance for any suggestion.
    Attached Files Attached Files

  2. #2
    Forum Contributor MariaPap's Avatar
    Join Date
    10-19-2013
    Location
    Chania-Crete-Greece
    MS-Off Ver
    Excel 2003-2010
    Posts
    319

    Re: Find the Next delivery date for a certain customer

    Any idea?

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: Find the Next delivery date for a certain customer

    It would be better if you used actual dates in column A (you can always format them to show only days if you wish), and you can use the =TODAY() function in F3, and then it would be possible to use an INDEX/MATCH formula to get the next date (and thus day) for a particular customer.

    Hope this helps.

    Pete

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365/2016
    Posts
    8,631

    Re: Find the Next delivery date for a certain customer

    Try this
    Enter in H3
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  5. #5
    Forum Contributor MariaPap's Avatar
    Join Date
    10-19-2013
    Location
    Chania-Crete-Greece
    MS-Off Ver
    Excel 2003-2010
    Posts
    319

    Re: Find the Next delivery date for a certain customer

    Many thanks to both of you for your reply.

    A "simple" INDEX & MATCH does not works in this xase.

    In sheet 1 i have the example for you Mr ALKEY

    In sheet 2, i did what you proposed Mr Pete.

    Pls take a look.
    Attached Files Attached Files

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    16,248

    Re: Find the Next delivery date for a certain customer

    The date for the Monday entry is 3rd December, so as today is 7th December then that can't be the next available date.

    You need to arrange your data so that it is in sequence, i.e. sort the data in columns A to C using A as the sort field. You should also have more realistic data, as the data only goes up to 7th December, and as that is today then there can't be a next delivery date for it.

    Hope this helps.

    Pete

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    5,983

    Re: Find the Next delivery date for a certain customer

    This would have been a lot easier if you had used actual dates instead of days of the week, but here is what I came up with.

    First, this implementation uses Excel Tables, so it will not work in Excel 2003. I use tables because they are easier to work with than ranges. This implementation also makes use of pivot tables and pivot tables built off Excel Tables, adjust to cover the number of rows when you add or delete rows from the tables.

    I also make use of a lot of helper cells.

    I created an Excel table in Columns J:K to assign numerical values to the day of the week.

    I converted the original data into an excel table and did a lookup against the day of the week to the the numerical value for the day in Column D.

    I have a small pivot table in column M - I overlaid this with a named dynamic range so I could use it for data validation in Cell G3. This isn't critical, but it does keep you from typing in the wrong thing. The list will grow or shrink as you add or delete customer ids to the main table. See this link to learn about named dynamic ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges.

    I also added data validation to cell F3 so you use a proper day of the week.

    So much for the cosmetics. Now for the logic.

    I built a larger pivot table in Columns O:P. This pivot table will work ONLY if there in no more than one delivery per customer per day. The pivot table is "rigged" to show the Customer ID and the days on which that customer has deliveries and a count of how many deliveries that customer has.

    There is a named dynamic range associated with this pivot table as well. It is called List_Delivery =OFFSET(Φύλλο1!$O$1,Φύλλο1!$G$5,0,Φύλλο1!$G$6,1). Some of these parameters are calculated in the helper cells in Column G.

    Cell G5 has the formula: =MATCH(G3,O:O,0) - this finds the row on which the selected customer ID occurs.

    Cell G6 has the formula: =INDEX(P:P,2,1) - this finds the number of deliveries the customer has.

    Cell G7 has the formula: =VLOOKUP(F3,Table_Days,2,FALSE) - this finds the day number of the selected day of the week

    Cell G8 has the formula: =G7+1 - this finds the earliest possible date for a delivery. Note that it might be day 8 (which is impossible, but we'll deal with this later)

    Cell G9 has the formula: =IFERROR(MATCH(G8,List_Delivery,-1),MATCH(MIN(List_Delivery),List_Delivery,-1)) - the first part of this formula says, find the row with the date in cell G8. If it is an exact match, use it. Otherwise "fall back" (go up a row) to the next higher date. The second part of the formula says, find the row with the minimum day number for that customer.

    In other words. Use tomorrow + 1 if it's there or use the next higher day number if it is not. If that fails, roll over to the first delivery day of the following week.

    Cell G10 has the formula: =INDEX(List_Delivery,G9,1) - now that we have the row, get the associated day value.

    Cell G11 has the formula: =INDEX(Table_Days,MATCH(G10,Table_Days[Value],0),1) - now that we have the day value, use the table to look up the day name.
    Attached Files Attached Files
    Last edited by dflak; 12-07-2018 at 11:50 AM.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  8. #8
    Forum Contributor MariaPap's Avatar
    Join Date
    10-19-2013
    Location
    Chania-Crete-Greece
    MS-Off Ver
    Excel 2003-2010
    Posts
    319

    Re: Find the Next delivery date for a certain customer

    dflak

    Thank you very much for your time and your work. I really appreciate this. Your explanations are very good and i get the point.

    As i have never used Pivot Tables and i have not the time to start to learn about them, i believe that i'll be able to modify your work without to use pivot table. Thanks again

    Mr Pete
    thank you for your suggestions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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