Vlookup with mutliple criteria and time period

[garion42]

Hi All!

We have an automation system that manages several hundred jobs each day. We need to record the start and end time for each job. Up to now this was done with an Excel sheet and we manually input the time. That wastes a lot of time each day, so we want to automate the work.

We have gotten to the point where the data is loaded into one sheet via MS-Query and using Vlookup, we load the start and end times in a different sheet. If the run time of the job is too long or the difference between expected start time and actual start time is too great (e.g. 30 minutes) we change the color of a specified field as an indicator that the job has a problem.

So far, that all works fine for jobs that run once a day. However, we have jobs that run several times a day, for example once an hour. We need a way to pick out the entry for a specific time. To make matters worse, the start time could vary +/- 10 minutes, so we need something check if the start time falls within a particular range.

So, in a nutshell, what I need is some way to check three cells with the third being a time range:

Col 1: automation_name (hardcoded)
Col 2: hostname (hardcoded)
Col 3: expected_start (hardcoded)
Col 4: actual_start (loaded from other sheet)

If automation_name matches
and hostname matches
and ( (actual_start >= expected_start-10min) and
(actual_start <= expected_start+10min)
then
write actual_start into cell
else
actual_start cell is red

Any help would be appreaciated.

 

[AnotherHiggins]

A formula cannot change the color of a cell. Have a look at Conditional Formatting (CF) for turning the cell red.

It sounds like you won't even need a column for the time-difference. You can just build the CF to change the color of the actual_start, or even the entire row, based on the difference between the expected_start and the actual_start.

Conditional Formatting has changed drastically as of Excel 2007. So before I attempt to help you come up with a formula, what version are you running?

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Hi,

You have "mixed metaphores" so to speak.

Here is your formula logic...
[tt]
It the 2 keys match then
return the actual start
Else
return WHAT?
[/tt]
Then there's the FORMATTING logic, assuming that SOME TIME is in the actual_start
[tt]
If ( (actual_start >= expected_start-10min) and
(actual_start <= expected_start+10min) Then
what
Else
what then
[/tt]
Third, realize that TIME is part of DATE and consequently, is stored in Excel as DAYS. So 10 minutes is 10/60/24 days, or it can be expressed in the TIME function as
[tt]
TIME(0,10,0)
[/tt]
If ALL the times in your workbook reference the SAME DAY, then you will not need the date part to calculate correctly. However, if you are calculating and comparing values from different days, then you MUST include the DATE part along with the TIME part of the Date/Time value.

Here's how I'd do the formula, using NAMED RANGES...
[tt]
Z2: =sumproduct((automation_name=A2)*(hostname=B2)*(actual_start))
[/tt]
will return actual_start or ZERO.
Then use Conditional Formatting to shade the cells based on this forumal > 0
[tt]
Formula Is: =sumproduct((automation_name=A2)*(hostname=B2)*(actual_start>=C2-TIME(0,10,0))*(actual_start<=C2+TIME(0,10,0)))
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

Thanks. I appreaciate all the help. In retrospect it does seem a little confusing.

I've gotten to the point where I can use CF to change the color of the cell. This is independent of the actual formual to get the starttime. The cell background is red if it is empty (i.e,job did not run) or white if there is a value in the cell. (we have Office 2003)

For the formular logic:

If automation_name and hostname match AND actual_start within 10 minutes then
return the actual start
Else
return NOTHING/NULL

One of the key aspects is that the automation can run multiple times during the day. For example, we have one automation that runs every hour. In our automation log (the Excel file) we have lines for each hour, so the actual_starttime needs to be filled with the starttime for the appropriate hour. However, the actual_starttime could be +/- 10 Minutes. For example, the automation that runs at 10AM could run between 09:50 and 10:10.

I am assumung that the purpose of sumproduct is simply to return 0 or 1.

I am pretty new at this, so I am not sure what this construct does:

(automation_name=A2)

Is this a shortend IF-statement. That is it returns 1 if the two terms match?

One thing I am not seeing here is the is the reference to the other sheet. That is, where is it comparign the data from the other sheet.

So what I think I need for part of this is a VLOOKUP based on multiple fields, but one of fields can be a range. I missing how this can be done.

 

[SkipVought]

Please post some sample data that demonstrates the multiple occurences you describe.

Please then explain what results you expect under these conditions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

I've upload a sample file. The DATA sheet is loaded from a Oracle database. The LOG sheet is what needs to record the start and end times of the various automations. The orginal file comes from a German version of Excel, so there are no formulas. Just the values. (sorry about that)

Looking at the DATA sheet, rows 8-11 are identical with the exception of the RUN_START and RUN_END column (when the automation started and ended). I concatenated the NAME and QUEUENAME columns (=B2&":"&D2) so that I would only have one value to search for.

On the LOG sheet I get the Start Time and End Time with this formula:
=VLOOKUP(A2&":"&B2;Table2!A1:F11;5;FALSE)

(I'm pretty sure it is VLOOKUP as I found a Germ->Eng translation list.)

I need to be able to match NAME, QUEUENAME and RUN_START, because a given automation can run multiple times during the day. Using VLOOKUP I always get the first match, for example 07:31:09.

The next problem is that I cannot match RUN_START 1:1 because it is a time which can vary +/- 10 Minutes in some cases, so I need to compare a range for the time.

To make things even more complex, the start times are not always round hours. It could be that a job runs every 15 minutes and the variation could +/- 5 minutes, for example.

 

[SkipVought]

Using VLOOKUP I always get the first match, for example 07:31:09. [/quote]
YES! How do you propose to differentiate WHICH one to use? There must be some logic.

I think that your workbook design might need to be looked at. Why are you doing lookups off you DATA sheeet? Please explain the transformation that need to happen after the DATA is imported?

For instance, you could easliy calculate the duration on DATA. How is Expected Start Time determined?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

The "logical" differentiation is that the combination NAME, QUEUENAME and RUN_START are unique. I can easily combine NAME and QUEUENAME to create a single value to make the lookups easier, but I still don't know how to integrate the RUN_START time in the lookup.

The reason I am doing the lookup off the DATA table is that this is automatically loaded from Oracle using MS-Query. We are planning to update every 15 minutes during the day because we need to see quickly if automations ran or not. All I am doing with the data is a straight lookup. Once I find the correct line, I read out RUN_START and RUN_END and write them into the LOG table.

Duration is something we might need in the future and, as you said, is easy. The Expected Start Time is more or less hardcoded using the formula TODAY()+offset, where offset is the HH:MM start, for example TODAY()+12:00 would mean the job is expected to start today a 12:00.

 

[SkipVought]

But you have not defined the logic for determining WHICH START TIME to use in a lookup.

You could have a counter for the unique combination and use it to lookup the specific occurence, using the OFFSET, INDEX, MATCH & COUNTA functions.

Would THAT logic work?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

>> But you have not defined the logic for determining WHICH START TIME to use in a lookup.

EXACTLY that's the problem. In my original post I said "We need a way to pick out the entry for a specific time." The rest is peanuts.

A counter might work. However, if we ever change the frequency, (i.e to every half-hour then the count changes). I was hoping for something that allowed us three variables and

>> Would THAT logic work?

Sorry, I don't see any logic, only a list of functions. (I'm not trying to be sarcastic) As I said in a previous post "I am pretty new at this".

 

[SkipVought]

It seems to me that if you queried your DATA sheet, you'd get nearly everything you need. Just add the EXPECTED Start. SIMPLE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

As far as I see it is not simple. There is no 1:1 relationship between the expected start time and the actual start_run time. As I said there can be a variance when the job starts. For example, the 12:00 run could start at 11:57:13 or 12:04:37.

I could concatenate the three variables in the LOG table, for example the 12:00 run: C2_PU_WM_IMPORT:C2_APP_41:1200 and use that for the lookup, but there is still no 1:1 correspondence. If the job runs at 11:57 and again at 12:59, which of these two does it take? As far as I see, this won't match anything in the DATA table.

If the jobs were always late, I could round down (12:04 => 12:00), but what about 11:57? What about a job that runs every 15 mnutes and has a variance of 5 minutes? (i.e. 12:10-12:20). How do I determine that the job that ran at 12:10 is actually the 12:15 run?

 

[SkipVought]

I would argue against lookups! It accomplishes NOTHING MORE than you already have.

YOU must figure out how your data works before you can figure out anything that Excel can help you with.

Apparently you have no way to relate these ACTUAL start times to what you EXPECT. That is YOUR problem! If you can't tell, I sure can't and neither can Excel!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[garion42]

>> I would argue against lookups!

Then I do not see a way to compare the list of what should run to the list of what actually did run, except continue to do it be hand.

> YOU must figure out how your data works before you can figure out anything that Excel can help you with.

I know of the data works. I am even to the point where I can do conditional formating or change the content fields based on the actual start time. (i.e. to report if the variance is to great). The problem is figuring out how to take the list of what should run and find the appropriate value in the list of what did run. Without the RUN_START time, it's easy. However, I have to consider the start time.

> Apparently you have no way to relate these ACTUAL start times to what you EXPECT.

I CAN relate them once I have found the appropriate line. For example, I can calculate the deviation between expected and actual.

> That is YOUR problem!

And that is the reason I turned to this forum. I was hoping someone would be willing to help me.

 

[SkipVought]

I am telling you that MS Query will get ALL the data into your sheet. It's just like making a copy and pasting it into the sheet, but much better.

Once you query your DATA sheet, your Duration and Expected Start Times will automatically calculate for all the rows of your query.

Check out faq68-5829

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]