First and Last instances

[byorj]

Hello,

Does anyone know a formula for getting the first and last instance of an agent in a data set, so I can record every agents log in and log out times from a big long list of all times

Thanks,

 

[CephasOz]

Hi byorj.

Have a look at this article by Bill: The VLOOKUP Slayer: XLOOKUP Debuts Excel

(It's a quick solution if XLOOKUP is available in your version of Excel.)

 

[byorj]

Struggling to get this working when there is multiple statuses.

I've removed names but the same people do recur again. Is there a way of logging their first time that isn't offline regardless of what the next status is?

 

[Snakehips]

I think it would help if you substituted the real names with eg. Agent1, Agent2 etc so that we can see how the rows of data relate to given agents. Also, illustrate the output that you would want to get from the sample data. Ideally, download XL2BB (in the posting Menu bar) so that members can easily replicate your example and, hopefully, find you a solution.

 

[byorj]

Sure, so the data set like this

and I wanted to get something like this as the result.

 

[Snakehips]

Then maybe something like below

Book1
	A	B	C	D	E	F	G	H
1	Agents	Status	Start	End		Agent	In	Out
2	Agent1	Offline	27/01/2020	02/02/2020 23:59:59		Agent1	07:43:03	07:58:35
3	Agent2	Offline	27/01/2020	02/02/2020 23:59:59		Agent2	05:00:41	07:43:03
4	Agent3	Offline	27/01/2020	28/01/2020 05:00:41		Agent3	06:21:06	08:15:34
5	Agent2	Chat	28/01/2020 05:00:41	28/01/2020 06:21:06
6	Agent3	Away	28/01/2020 06:21:06	28/01/2020 06:34:30
7	Agent2	Chat	28/01/2020 06:34:30	28/01/2020 07:09:09
8	Agent3	Meeting/1:1	28/01/2020 07:25:31	28/01/2020 07:42:59
9	Agent2	Available	28/01/2020 07:42:59	28/01/2020 07:43:03
10	Agent1	On A Call	28/01/2020 07:43:03	28/01/2020 07:52:08
11	Agent3	After Call Work	28/01/2020 07:52:08	28/01/2020 07:54:07
12	Agent1	Available	28/01/2020 07:54:07	28/01/2020 07:54:12
13	Agent1	On A Call	28/01/2020 07:54:12	28/01/2020 07:58:35
14	Agent3	After Call Work	28/01/2020 07:58:35	28/01/2020 08:00:35
15	Agent3	Available	28/01/2020 08:15:34	28/01/2020 08:15:34
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	{=INDEX($C$1:$C$15,MATCH(1,($A$1:$A$15=F2)*($B$1:$B$15<>"Offline"),0))}
H2:H4	H2	{=INDEX($D$1:$D$15, MATCH(2,1/($A$1:$A$15=F2)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hope that helps.

 

[byorj]

That works great for the start, but I get all off the offlines at

2020-02-02 23:59:59

Is there a way to include the offline too into the end?

 

[Snakehips]

I'm sorry but I do not understand.

 

[byorj]

it shows like this as offline is included as a status in end, is there a way to remove this from out as well

 

[Snakehips]

Try..

Book1
	A	B	C	D	E	F	G	H
1	Agents	Status	Start	End		Agent	In	Out
2	Agent1	Offline	27/01/2020	02/02/2020 23:59:59		Agent1	07:43:03	07:58:35
3	Agent2	Offline	27/01/2020	02/02/2020 23:59:59		Agent2	05:00:41	07:43:03
4	Agent3	Offline	27/01/2020	28/01/2020 05:00:41		Agent3	06:21:06	08:15:34
5	Agent2	Chat	28/01/2020 05:00:41	28/01/2020 06:21:06
6	Agent3	Away	28/01/2020 06:21:06	28/01/2020 06:34:30
7	Agent2	Chat	28/01/2020 06:34:30	28/01/2020 07:09:09
8	Agent3	Meeting/1:1	28/01/2020 07:25:31	28/01/2020 07:42:59
9	Agent2	Available	28/01/2020 07:42:59	28/01/2020 07:43:03
10	Agent1	On A Call	28/01/2020 07:43:03	28/01/2020 07:52:08
11	Agent3	After Call Work	28/01/2020 07:52:08	28/01/2020 07:54:07
12	Agent1	Available	28/01/2020 07:54:07	28/01/2020 07:54:12
13	Agent1	On A Call	28/01/2020 07:54:12	28/01/2020 07:58:35
14	Agent3	After Call Work	28/01/2020 07:58:35	28/01/2020 08:00:35
15	Agent3	Available	28/01/2020 08:15:34	28/01/2020 08:15:34
16	Agent3	Offline	02/02/2020 23:59:59	02/02/2020 23:59:59
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	{=INDEX($C$1:$C$16,MATCH(1,($A$1:$A$16=F2)*($B$1:$B$16<>"Offline"),0))}
H2:H4	H2	{=INDEX($D$1:$D$16, MATCH(2,1/(($A$1:$A$165=F2)*($B$1:$B$16<>"Offline"))))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.