hstack/index function with duplicate values-CodePudding

Is there a way I can populate all the Agents to Table 2 that is currently on "On Call" status?

I am currently using HSTACK and INDEX function. If the agent's status is "On Call" from Table 1, all agents and their corresponding duration would populate Table 2 as shown in the picture.

Now my problem is there are instances that agents would have the same duration. If this happens, my formula would only return the first value from Table 1 for the Agents instead of all the Agents regardless if they have the same value for duration or not as long as their STATUS is "On Call".

See on the picture, it only return Agent 5 with the duration 0:02:40, instead of Agent 5, Agent 8, and Agent 11 on that column.

How would I be able to return all the value for the Agent with duplicate value for their duration?

Here is the exact formula I am currently using on cell E4 right now

=HSTACK(INDEX($A$4:$C$14,MATCH(SORT(FILTER($C$4:$C$14,($B$4:$B$14=E2),""),1,-1),$C$4:$C$14,0),1),TEXT(SORT(FILTER($C$4:$C$14,($B$4:$B$14=E2),""),1,-1),"[h]:mm:ss"))

CodePudding user response：

You can use either of the one as per your wish:

As suggested by Scott Craner Sir,

• Formula used in cell E4

=SORT(FILTER(HSTACK($A$4:$A$14,$C$4:$C$14),$B$4:$B$14=E2),2,-1)

As suggested by VBasic2008 Sir,

• Formula used in cell K4

=SORT(CHOOSECOLS(FILTER(A4:C14,B4:B14=K2),1,3),2,-1)

Or,

• Formula used in cell H4

=CHOOSECOLS(SORT(FILTER(A4:C14,B4:B14=H2),3,-1),1,3)