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

Hi Pete,

What you would be best to use is an array formula. When you enter an
array formula, you press Ctrl+Shift+Enter instead of just Enter. (Some
people call them CSE formulas because of this key combo).

I won't go too deep into specifics of how they work as there are much
better people and websites to check out.

The formula you would enter is:

=MIN(IF($H$2:$H$5H2,$H$2:$H$5,""))

Where Column H has your start times and H2 is the start time of the
current order. After you press Ctrl+Shift+Enter, it will show curly
braces around it:

{=MIN(IF($H$2:$H$5H2,$H$2:$H$5,""))}

The array formula works by calculating each cell of the array
separately. So the IF statement is like having this instead:

If ($H$2H2,$H$2,"")
If ($H$3H2,$H$3,"")
If ($H$4H2,$H$4,"")
If ($H$5H2,$H$5,"")

It then takes the result of that IF statement (either the cell value,
or "") and finds the minumum (lowest) number. This is the next time in
the series.

This will work for you only if there is no crossing over to the next
day. That would require storing dates as well as times, but the
formula should still work.

Happy to send you the workbook that I used to work on this if you
would like an example. Drop me an email with:

The USENET Group Name AND the Subject Header in the subject line
Your Email Address
A copy of your original post.

Cheers,
Nick



Then the MIN formula finds the lowest time (earliest) from the
remaining ones.

This is ok if you are only looking at say one machine. If you have
multiple machines then you would need separate tables for each one.
There might be better solutions - anyone?



On Nov 13, 8:07*pm, 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

Hi Pete,

What you would be best to use is an array formula. When you enter an
array formula, you press Ctrl+Shift+Enter instead of just Enter. (Some
people call them CSE formulas because of this key combo).

I won't go too deep into specifics of how they work as there are much
better people and websites to check out.

The formula you would enter is:

=MIN(IF($H$2:$H$5H2,$H$2:$H$5,""))

Where Column H has your start times and H2 is the start time of the
current order. After you press Ctrl+Shift+Enter, it will show curly
braces around it:

{=MIN(IF($H$2:$H$5H2,$H$2:$H$5,""))}

The array formula works by calculating each cell of the array
separately. So the IF statement is like having this instead:

If ($H$2H2,$H$2,"")
If ($H$3H2,$H$3,"")
If ($H$4H2,$H$4,"")
If ($H$5H2,$H$5,"")

It then takes the result of that IF statement (either the cell value,
or "") and finds the minumum (lowest) number. This is the next time in
the series.

This will work for you only if there is no crossing over to the next
day. That would require storing dates as well as times, but the
formula should still work.


Happy to send you the workbook that I used to work on this if you
would like an example. Drop me an email with:

The USENET Group Name AND the Subject Header in the subject line
Your Email Address
A copy of your original post.

Cheers,
Nick

"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

.

Hi Again Pete,

Sorry my email is .

Cheers,
Nicholas Perkins

http://www.nicholasperkins.com/blog/

"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

.

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

On Fri, 13 Nov 2009 06:41:42 -0500, Ron Rosenfeld
wrote:

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,Tim e,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_Ti me,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Ti me,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Tim e,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_ Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron

I forgot to write that you enter the above formulas in columns in the first
row; then select them all and copy/drag (fill) down as far as required (or
until you start seeing blanks in the Start_time column).

Also, you will need to format the Start Time and End Time columns as Time.
--ron

On Nov 13, 2:03*pm, Ron Rosenfeld wrote:
On Fri, 13 Nov 2009 06:41:42 -0500, Ron Rosenfeld
wrote:





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,Tim e,0)),"")

* *Excel 2003 and earlier:
=IF(ISNA(INDEX(Planned_order,MATCH(Start_Time,Tim e,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_Ti me,Time,0)),"")

* *Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Ti me,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Tim e,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_ Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron

I forgot to write that you enter the above formulas in columns in the first
row; then select them all and copy/drag (fill) down as far as required (or
until you start seeing blanks in the Start_time column).

Also, you will need to format the Start Time and End Time columns as Time..
--ron- Hide quoted text -

- Show quoted text -

Thanks Ron, I'll give it a whirl.

Hi Pete try to copy (Cell) start Time of 2nd product & past link to the cell
1st product It looks simple . whenever you will enter the start time of your
product this start time will be the end time of your previous product.

Khusro

"Pete" wrote:

On Nov 13, 2:03 pm, Ron Rosenfeld wrote:
On Fri, 13 Nov 2009 06:41:42 -0500, Ron Rosenfeld
wrote:





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,Tim e,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_Ti me,Time,0)),"")

Excel 2003 and earlier:
=IF(ISNA(INDEX(Product_Description,MATCH(Start_Ti me,Time,0))),
"",INDEX(Product_Description,MATCH(Start_Time,Tim e,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_ Time,Time,0))=0,
"",INDEX(Volume,MATCH(Start_Time,Time,0))))
--ron

I forgot to write that you enter the above formulas in columns in the first
row; then select them all and copy/drag (fill) down as far as required (or
until you start seeing blanks in the Start_time column).

Also, you will need to format the Start Time and End Time columns as Time..
--ron- Hide quoted text -

- Show quoted text -

Thanks Ron, I'll give it a whirl.
.