On Fri, 13 Nov 2009 02:07:50 -0800 (PST), Pete
wrote:

I have a table of data for my Scheduled cutting list that is exported
from another program into an Excel Spreadsheet and from this table I
would like to be able to find the start and end time of the production
runs automatically in a separate table.

The table is set up like this

Planned order Time Product Description Volume
12345 08:00:00 Product 1
3500
23435 11:32:00 Product 2 5680
14567 13:24:00 Product 3 10000

and so on.

I can find the start time by using Vlookup( Planned order,Datatable,
2,false) which returns 08:00:00. The end time of Product 1 is the
start time of the next planned order for Product 2 etc etc

What I would like is a table that looks like this:

Planned order Start time End time Volume
12345 08:00:00 11:32:00 3500
23435 11:32:00 13:24:00 5680
14567 13:24:00 22:00:00 10000

I hope I've explained this ok

Regards

Pete

The "best" formulas depend on whether you are working with Excel 2007+ or
earlier versions. 2007+ has the IFERROR function, and earlier versions do not.

I used NAME'd ranges for the variables. You can either NAME the ranges, or use
cell references.

I don't know how you determine the End Time of the last entry; so in your
example, the formula will return a #NUM! error. This can be altered depending
on your specifics.

There is error checking so as to return blanks for entries that are not
present.

Here are the formulas:

Planned Order:
Excel 2007+:
=IFERROR(INDEX(Planned_order,MATCH(Start_Time,Time ,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Planned_order,MATCH(Start_Time,Time ,0))),
"",INDEX(Planned_order,MATCH(Start_Time,Time,0 )))

Start Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:1 )))

End Time:
=IF(COUNT(Time)<ROWS($1:1),"",SMALL(Time,ROWS($1:2 )))

Product Description:
Excel 2007+
=IFERROR(INDEX(Product_Description,MATCH(Start_Tim e,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Tim e,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Time ,0)))

Volume:
Excel 2007+:
=IFERROR(IF(INDEX(Volume,MATCH(Start_Time,Time,0)) =0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))),"")

Excel 2003 and earlier
=IF(Start_Time="","",IF(INDEX(Volume,MATCH(Start_T ime,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron