Gantt chart with conditional formatting, multiple cirteria and duplicate data. :) array, index, aggregate

[MrMagnifico]

Hello,

I have come close to what I need but need help on the last bit.
I hope someone can please help me solve this.
I need to produce a gantt chart for batch jobs that run multiple times a day and show that out for a week.

So job1 will have multiple rows for each start and end time for each time it ran for each day.
job1 will need to be charted on the same row in the gantt chart.
There are 4 criteria to match. (group, set, client, subclient)

Data will look like...
group set client subclient start End
cvm01 DB job1 default 3-15-13 19:00 3-15-13 22:00
cvm01 DB job1 default 3-16-13 18:00 3-16-13 20:00
cvm01 DB job1 default 3-16-13 22:00 3-17-13 06:00
cvm01 DB job1 default 3-17-13 22:00 3-17-13 23:30


I would like the chart to look like...
group set client subclient | date time columns incrementing---> | | | | | |
cvm01 DB job1 default | conditional formatting applied if all criteria true.
cvm01 DB job2 default | conditional formatting applied if all criteria true.
cvm01 DB job3 custom | conditional formatting applied if all criteria true.
cvm02 file job4 default | conditional formatting applied if all criteria true.

I am using conditional formatting that if...
my data matches the four criteria in the unique chart then color it in.

So return all rows that (group, set, client, subclient all match) and compare each row's start and end time to see if it is greater than or less than the date and time in that particular column. If the time falls between the start and end time then it get formatted and thus plots out the run times.


Here is what I have so far, thanks to excel magic tricks.. using index and aggregate
a is the group column from the data list
b is the set column from the data list
c is the client column from the data list
d is the subclient column from the data list
k is the start time column from the data list
L is the end time column from the data list

n is the group column on the gantt chart unique list
o is the set column on the gantt chart unique list
p is the client column on the gantt chart unique list
q is the subclient column on the gantt chart unique list
T is the first time column of the gantt chart

=AND(T$1>=INDEX($K$2:$K$1200,AGGREGATE(15,6,(ROW($K$2:$K$1200)-ROW($K$2)+1)/($N2&$O2&$P2&$Q2=$A$2:$A$1200&$B$2:$B$1200&$C$2:$C$1200&$D$2:$D$1200),1)),T$1<=INDEX($L$2:$L$1200,AGGREGATE(15,6,(ROW($L$2:$L$1200)-ROW($L$2)+1)/($N2&$O2&$P2&$Q2=$A$2:$A$1200&$B$2:$B$1200&$C$2:$C$1200&$D$2:$D$1200),1)))


The problem is that this formula increments which row to use by counting up each time the formula is paste into the next cell. I would like the formula to examine each row like an OR function. So , does the time in the chart fall between the start and end time for each row found... like row one OR row two OR row three etc? if so, format the cell..

 

[MrMagnifico]

The formula I am using is explained here starting at 5:15 min youtube video -- excel magic trick 822
What I need to know is at 11:20 where he is using "columns" for the k value....
columns increments each time the formula is copied over to the next cell.
Instead, is there a way to test each row from the array in the same cell like... is the 1st row true or the 2nd or the 3rd and so on?

 

[MrMagnifico]