Extract From Previous Row When Time Is Greater Than

[dharmaskibum]

Column A has a run# and column AA has a date & time stamp. We can call this workbook Log. Example:

____A___AA___________
1___EK-01___10/19 17:50
2___EK-02___10/19 18:00
3___EK-03___10/19 18:17
4___EL-01___10/20 17:30
5___EL-02___10/20 17:55
6___EL-03___10/20 18:22

In a seperate workbook, I have the date in column D. In column E, I would like to extract the run# of the last run before 18:00 on that date. Example:

___D_____E
1__10/19__EK-01
2__10/20__EL-02

Any suggestions on how to do this?

[DonkeyOte]

One approach - in the "separate" workbook:

E1:
=LOOKUP(2,1/('YourFilePath[filename.xls]sheetname'!$AA$1:$AA$100<$D1+"18:00"),'YourFilePath[filename.xls]sheetname'!$A$1:$A$100)
copied down

Note the above is not very efficient so try to avoid making the ranges overly large
(you could also use a Max Array)

For simplicity I would suggest creating the above with both files open (short syntax) - once you close the workbook Log the full file path will be created for you.

[outofthehat]

1. Create a new column (e.g. AB) which puts in the date if it's the last batch before 6pm, otherwise a dash. See attached sheet (sorry, UK formatted time, with day number first, then month). The logic is as follows:

• if the time on this row is less than 6pm and the time on the next row is greater than 6pm, then put in the date, otherwise put a dash

You can get just the time from a date/time combination by using MOD(), and the date from the same by using INT().

NB If you think there is a possibility that the last batch on a day starts before 18:00, then you also need to check the date, not just the time.

2. Left-hand column of the results table (yellow on attached) is just a list of dates. So, for each date, use MATCH to find out where that date appears in your new column AB, then INDEX to find the corresponding value in the list of batch numbers.

[outofthehat]

Donkey's is much more elegant! And of course it shows use of LOOKUP() instead of combining INDEX() and MATCH() - would look better if I rewrote mine... so attached.

Donkey, would appreciate some comment on 'efficiency', as a general point of information...

[DonkeyOte]

would appreciate some comment on 'efficiency', as a general point of information...

Helpers are nearly always worthwhile for sake of efficiency here being no exception and the use of LOOKUP in your example is good as here it is being used in it's most efficient form*

One tweak would be to your AB formulae - you need an OR within the AND I think ?

=IF(AND(MOD(AA1,1)<TIME(18,0,0),MOD(AA2,1)>=TIME(18,0,0)),INT(AA1),"-")

should I think be:

=IF(AND(MOD(AA1,1)<TIME(18,0,0),OR(INT(AA1)<>INT(AA2),MOD(AA2,1)>=TIME(18,0,0))),INT(AA1),"-")

* unlike my earlier example which though negating helpers is not efficient - esp. with large [external] datasets

[outofthehat]

Agreed, the OR tweak deals with my earlier NB comment

NB If you think there is a possibility that the last batch on a day starts before 18:00, then you also need to check the date, not just the time.

Is it the fact that the large data set is external that makes things inefficient? If so, I guess it would be better to do the LOOKUP on a separate sheet of the original book, then use a simple formula to pull the summary data through to the other book?

[DonkeyOte]

Is it the fact that the large data set is external that makes things inefficient?

The LOOKUP(2,1/(...)) construct is not efficient irrespective of it's location relative to the data.