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

Peaceless

New Member
Joined
Sep 6, 2013
Messages
29
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?
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

eb101

Board Regular
Joined
Sep 25, 2014
Messages
62
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

New Member
Joined
Sep 6, 2013
Messages
29
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

Board Regular
Joined
Sep 25, 2014
Messages
62
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

New Member
Joined
Sep 6, 2013
Messages
29
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top