Finding first activity type for a particular status key.

jackfox68

Board Regular
Joined
Jan 24, 2010
Messages
120
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
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:p></o:p>

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:p></o:p>
Any help, ideas, suggestions would be appreciated<o:p></o:p>
Excel Workbook
ABCD
1User idState Start (Date/time)LoggedIn (Flag)Status key
2aaraya11/8/2011 03:25:45 PM1Gone Home
3aaraya11/8/2011 03:25:45 PM1Gone Home
4aaraya11/8/2011 04:23:25 PM1Available
5aaraya11/8/2011 04:54:08 PM1Follow Up
6aaraya11/8/2011 04:54:38 PM1Available
7dgamboa11/8/2011 02:05:00 PM1Gone Home
8dgamboa11/8/2011 02:05:00 PM1Gone Home
9dgamboa11/8/2011 02:05:04 PM1Available
10dgamboa11/8/2011 03:13:32 PM1Follow Up
11dgamboa11/8/2011 03:14:32 PM1Available
12dgamboa11/8/2011 04:14:33 PM1Follow Up
13jlandero11/8/2011 02:23:50 PM1Gone Home
14jlandero11/8/2011 02:23:50 PM1Gone Home
15jlandero11/8/2011 02:30:25 PM1Available
16jlandero11/8/2011 02:50:03 PM1Follow Up
17jlandero11/8/2011 02:51:03 PM1Available
18jlandero11/8/2011 03:34:20 PM1Follow Up
19jlandero11/8/2011 03:35:20 PM1Available
20jlandero11/8/2011 03:43:57 PM1Follow Up
Sheet2
Excel 2010
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Board Regular
Joined
Jan 24, 2010
Messages
120
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Board Regular
Joined
Jan 24, 2010
Messages
120

ADVERTISEMENT

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

Thanks again
 

jackfox68

Board Regular
Joined
Jan 24, 2010
Messages
120

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
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

Board Regular
Joined
Jan 24, 2010
Messages
120
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,361
Members
414,139
Latest member
okela0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top