Finding first activity type for a particular status key.

[jackfox68]

I have an activity report I use to determine the time an employee logs into the phones system, and what time they puts themselves into a status that makes them available to receive calls. Currently this is a manual process. I would like to automate this process using excel, if at all possible. I need excel to show me the first time an employee meets the following combined criteria:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Logged in = 1
Status Key = Available
(For example: dgamboa would return the value in cell B9 because the “Logged in(Flag)” value equals “1” and the “Status key” value equals “Available”<o></o>
I have a list of user ids I can copy and paste into another sheet in excel. I would like the results to be shown next to the corresponding user id<o></o>
Any help, ideas, suggestions would be appreciated<o></o>

Excel Workbook
	A	B	C	D
1	User id	State Start (Date/time)	LoggedIn (Flag)	Status key
2	aaraya	11/8/2011 03:25:45 PM	1	Gone Home
3	aaraya	11/8/2011 03:25:45 PM	1	Gone Home
4	aaraya	11/8/2011 04:23:25 PM	1	Available
5	aaraya	11/8/2011 04:54:08 PM	1	Follow Up
6	aaraya	11/8/2011 04:54:38 PM	1	Available
7	dgamboa	11/8/2011 02:05:00 PM	1	Gone Home
8	dgamboa	11/8/2011 02:05:00 PM	1	Gone Home
9	dgamboa	11/8/2011 02:05:04 PM	1	Available
10	dgamboa	11/8/2011 03:13:32 PM	1	Follow Up
11	dgamboa	11/8/2011 03:14:32 PM	1	Available
12	dgamboa	11/8/2011 04:14:33 PM	1	Follow Up
13	jlandero	11/8/2011 02:23:50 PM	1	Gone Home
14	jlandero	11/8/2011 02:23:50 PM	1	Gone Home
15	jlandero	11/8/2011 02:30:25 PM	1	Available
16	jlandero	11/8/2011 02:50:03 PM	1	Follow Up
17	jlandero	11/8/2011 02:51:03 PM	1	Available
18	jlandero	11/8/2011 03:34:20 PM	1	Follow Up
19	jlandero	11/8/2011 03:35:20 PM	1	Available
20	jlandero	11/8/2011 03:43:57 PM	1	Follow Up
Sheet2

Excel 2010

 

[Marcelo Branco]

Assuming your list in column A, headers in row 1, maybe this array-formula in B2 copied down

=INDEX(Sheet2!$B$2:$B$20,MATCH(1,IF(Sheet2!$A$2:$A$20=A2,IF(Sheet2!$C$2:$C$20=1,IF(Sheet2!$D$2:$D$20="Available",1))),0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

copy down

M.

 

[jackfox68]

Marcelo - when you say "assuming my list in column A" are you referring to the list of user id's I have for all of my agents? If so I would keep that on a sperate sheet.

 

[Marcelo Branco]

Marcelo - when you say "assuming my list in column A" are you referring to the list of user id's I have for all of my agents? If so I would keep that on a sperate sheet.

Yes, exactly like

Sheet1

A B
<TABLE style="WIDTH: 142pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=189><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>User id</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 94pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=125>First Activity</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>aaraya</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>11/8/11 4:23:25 PM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>dgamboa</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>11/8/11 2:05:04 PM</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20>jlandero</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>11/8/11 2:30:25 PM</TD></TR></TBODY></TABLE>

Formula in B2 copied down

Remak: the dates above are dd/mm/yyyy because this is my regional setting for dates. Just apply the formula and, i think, it will work - dont forget to confirm with Ctrl+Shift+Enter simultaneously because it's an array formula

 

[jackfox68]

Thanks Marcelo. Will let you know how it works out! Moving from intelectual labor (excel) to manual labor ( cutting firewood ) --- haa haa!

Thanks again

 

[Marcelo Branco]

You are welcome.

Hope it works and good luck in the new "job" ha, ha...

M.

 

[jackfox68]

No luck Marcelo. I am pretty sure I am entering everything correctly but I am getting an #N/A error. I even tried moving the list to the same sheet and editing the code and still not luck. Where you able to get it to work on your end?

 

[Marcelo Branco]

Maybe you have some extraneous leading/trailing spaces in your data.

Just in case, try this

=INDEX(Sheet2!$B$2:$B$20,MATCH(1,IF(TRIM(Sheet2!$A$2:$A$20)=TRIM(A2),IF(TRIM(Sheet2!$C$2:$C$20)=1,IF(TRIM(Sheet2!$D$2:$D$20)="Available",1))),0))

Ctrl+Shift+Enter

 

[Marcelo Branco]

There is a wrong space in the formula

=INDEX(Sheet2!$B$2:$B$20,MATCH(1,IF(TRIM(Sheet2!$A$2:$A$20)=TRIM(A2),IF(TRIM(Sheet2!$C$2:$C$20)=1,IF(TRIM(Sheet2!$D$2:$D $20)="Available",1))),0))

Delete it

 

[jackfox68]

I had thought the same thing that you mentioned, nothing leading or trailing the data. I just tried the formular you sent as well, still no luck. Is there a way I can send the sheet to you using the forum?