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.

Any idea?

Try this
Enter in H3

Formula:

Please Login or Register  to view this content.

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.

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.

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.