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:

Please Login or Register  to view this content.

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 ?

Please Login or Register  to view this content.

should I think be:

Please Login or Register  to view this content.

* 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.

[dharmaskibum]

Thanks DonkeyOte--helpful again.

I do not understand the inefficiencies of LOOKUP(2,1/(...)) though. I'm assuming that is why it works for a range of ~100, but anything larger returns 0? Unfortunately, that does not work for me.

While outofthehat's solution works, I am limited by what I can add to the Log worksheet. I have tried using a MAX(IF()) array assuming that was what DonkeyOte was refering to, but that is returning 0 as well.

[DonkeyOte]

I do not understand the inefficiencies of LOOKUP(2,1/(...)) though. I'm assuming that is why it works for a range of ~100, but anything larger returns 0?

You can use a much bigger range if necessary... if you're getting a 0 result it will be for a reason.

...I have tried using a MAX(IF()) array assuming that was what DonkeyOte was refering to, but that is returning 0 as well.

As above... I would suggest posting samples file to illustrate the problem "in action"

[dharmaskibum]

Ok, my fault. I had inefficient = will not work on larger ranges in my head and that stopped me from trouble shooting correctly.

I realize now that the issue is when AA does not have a time stamp. I would like the range to include future runs that will occur later in the month. If I set the range that large, and AA has no time stamp, it gives me 0. Also, if there is a break in the time stamps (for test runs), it will give the last available run# recorded. If I set the range to just include production runs that have been completed (and therefore have a time stamp), then it works.

Attached is a sample.

[DonkeyOte]

I realize now that the issue is when AA does not have a time stamp.

To ignore blanks (AA) add an ISNUMBER test (this prevents incomplete logs being included)

Please Login or Register  to view this content.

Also, if there is a break in the time stamps (for test runs), it will give the last available run# recorded.

I can't determine if the above is a requirement or not ?
The above function will return the last Run # where Time < criteria irrespective of date.

[dharmaskibum]

^^ Not a requirement. That is what it was doing--did not want it to do that.

Your solution works perfectly--thank you. I must have tried ISNUMBER at least 10-times last night thinking that would work, but it kept giving me a too many functions error. I must have been making a typo somewhere. Thank you again for your help.