Finding min or max in a range where both rows and columns change

[Peaceless]

Hi!

I´m looking for a solution for a control check I´m doing. I have workbook1 with several hundred time series. Each column has a series header and each row has a date. The dates changes every day, removing one and adding the next one as each day passes. The columns changes as well, adding and removing time series as these are added and removed in my database.

In my control workbook I have for each row two dates, begining and end, a value to check for and one or several values that correspond to the headers in workbook1. These rows changes as well as Items are added and removed from the database.

Example
Workbook 1
A B C D
Date X Y Z
2016-01-01 1 20 72
2016-01-02 2 45 85
2016-01-03 3 25 130
2016-01-04 5 19 70
2016-01-05 7 20 69
.
.
2016-02-26

Workbook control
A B C D E F G H I
Item Begining date End date value1 Control value1 value2 control value2
1. 2016-01-02 2016-03-04 X 0,5 Y 40
2. 2016-01-03 2016-01-04 Z 75 NA NA


For the above example I want to check if the data points in the time series in workbook 1 at any date in the range has been below the control value.
so for Item 1 I want to check if value 1 has been below control value 1 in the range 2016-01-02 up until the last recorded value in the range for X in workbook 1. I only want the first value that fulfills the breach.
The result for item 1 would be:
value X NO
value Y YES 2016-01-03 25

The result for item 2 would be:
value Z YES 2016-01-04 70

As said next day, Y might have disappeared from the database and Z would be next to X in workbook one

What´d think? Any ideas how to solve this?

 

[eb101]

I might not have interpreted this correctly so I apologize in advance. If you are checking values against a set range to see if they meet yes or no I would suggest setting those ranges and using a vlookup and using "TRUE" in [range look up].

For example, I look at changing values for inbound after call and outbound wrap data, I assign a range for each value and look up U=Unacceptable or AE=Achieves Expectations for each field to determine if the agent needs to be audited for call avoidance.

 

[Peaceless]

I might not have interpreted this correctly so I apologize in advance. If you are checking values against a set range to see if they meet yes or no I would suggest setting those ranges and using a vlookup and using "TRUE" in [range look up].

For example, I look at changing values for inbound after call and outbound wrap data, I assign a range for each value and look up U=Unacceptable or AE=Achieves Expectations for each field to determine if the agent needs to be audited for call avoidance.

are your ranges changeing? I mean the rows and columns might change everyday so I need the formula to change with it. One day a coulumn header will be in the H column, next in the Z column. same for the days. The days to look at also changes between items but all will start somewhere in the original timeseries, but that row changes. One thing they have incommon is that they all include the last available value in the timeseries (t-1 in my case). Does that sound like your set-up?

 

[eb101]

are your ranges changeing? I mean the rows and columns might change everyday so I need the formula to change with it. One day a coulumn header will be in the H column, next in the Z column. same for the days. The days to look at also changes between items but all will start somewhere in the original timeseries, but that row changes. One thing they have incommon is that they all include the last available value in the timeseries (t-1 in my case). Does that sound like your set-up?

My column's headers do not change. Data changes daily, but columns do not move and the range checker only changes if I choose to change what is unacceptable as a value. If the column names stay the same you can try to pivot the data and refresh. It should keep the columns in the pivot the same. I never tried it so I am not sure if that would work. I would find out who is querying the data and see if there is a reason why the column names are moved around. This could happen if different people are setting different parameters when executing the data.

Do you get the file directly emailed? Pull it from a sharepoint? It always helps to reach out to IT to figure out that first variable.

 

[Peaceless]

My column's headers do not change. Data changes daily, but columns do not move and the range checker only changes if I choose to change what is unacceptable as a value. If the column names stay the same you can try to pivot the data and refresh. It should keep the columns in the pivot the same. I never tried it so I am not sure if that would work. I would find out who is querying the data and see if there is a reason why the column names are moved around. This could happen if different people are setting different parameters when executing the data.

Do you get the file directly emailed? Pull it from a sharepoint? It always helps to reach out to IT to figure out that first variable.

hmm, ok can look in to the pivot, that might help.
The reason it changes is because I pull the data to excel via SQL from the database. It changes since new timeseries are added and some are removed, it´s to save space/time, the query takes 1,5 min to update now so want it to be as lean as possible.