Auto populate a table based from datas from another table-CodePudding

this is the another version of my first question and I hope I can best explain my problem this time.

From the Table 1, I want to auto populate Table 2 based on this conditions and criteria (below)

From the example, I basically have 3 initial criteria, ON CALL, AVAILABLE, and BREAK

Now for the conditions, I want all Agents from status ON CALL, AVAILABLE, BREAK from Table 1 to be populated on Table 2 (optional: If possible, I wanted only to show agents that HAS a duration of 4 minutes and above from each status). My problem is I always refresh TABLE 1 so I can get an updated data. My goal here is to monitor our agents their current Status and Running Duration, and from that I only need to check on the table 2 so I would see right away who has the highest running duration from each status to be called out.

I only tried MAXIFS function but my problem with it, I can only show 1 result from each status.

What I wanted is to fully populate Table 2 from the data on Table 1. If this is possible with ROW function that would be great, because what I really wanted is a clean Table, and it should only load data if the criteria is met.

Thank you

CodePudding user response：

Something you may be interested in doing is utilizing HSTACK. I am not sure how you are currently obtaining the Agents name in the adjacent column to the results but this would populate both the Agent along with the Duration.

=HSTACK(INDEX(A:C,MATCH((TIMEVALUE(TEXT(SORT(FILTER(C:C,(C:C>=TIMEVALUE("00:04:00"))*(B:B=H2),""),1,1),"h:mm:ss"))),C:C,0),1),TEXT(SORT(FILTER(C:C,(C:C>=TIMEVALUE("00:04:00"))*(B:B=H2),""),1,1),"h:mm:ss"))

This formula checks Table 1 for any Agent with the status referenced in H2 (Available) that also has a time greater than or equal to 4 mins. It then sorts the results in ascending order and populates the Agent Name that is associated with it. It is dynamic and will produce a table like the following:

Just update the formula to check for "On Call" and "BreaK" as desired for the other two.

UPDATE:

As for conditional formatting, this is utilizing the custom formula posted in the comments. If the formatting of the times are of [h]:mm:ss then you would be looking to do something like this. Notice the 2 cells are highlighted for being between 4 mins and 5 mins.