repost: Min/Max help needed with a SumProduct

[Guest]

Hello Excel Yoda’s

For me this is an impossibility, but I know for some of you the force will
guide you to the answer. The first table shows my data. Please keep in mind
that immediately following Associate 1 there will be associate 2, Associate
3, etc…

Here is how I would like to return the data;
A B C
D
Associate first logon time Lunch Duration Last Logoff time

The associate name will already be listed in the A: column, so the times
will likely need to match the A: entry. The total lunch time can be
calculated by taking the entry marked “2LUN†and subtracting that time from
the first “Available†time following the “2LUNâ€

Bottom line is I do not know how to write min/max to match a criteria with a
sumproduct. I know I ask a lot, but I really could use the help and
appreciate your effort.

A B C D
Name Event Type Date Start Time
Associate 1 LOGON 2/8/2006 6:58:54
Associate 1 LOGOFF 2/8/2006 6:59:03
Associate 1 LOGON 2/8/2006 6:59:06
Associate 1 LOGON 2/8/2006 6:59:07
Associate 1 LOGON 2/8/2006 6:59:14
Associate 1 8WRP 2/8/2006 8:41:20
Associate 1 AVAILABLE 2/8/2006 8:43:46
Associate 1 1BRK 2/8/2006 9:27:51
Associate 1 AVAILABLE 2/8/2006 9:40:25
Associate 1 8WRP 2/8/2006 10:05:09
Associate 1 AVAILABLE 2/8/2006 10:07:28
Associate 1 8WRP 2/8/2006 10:20:32
Associate 1 AVAILABLE 2/8/200 10:27:58
Associate 1 8WRP 2/8/2006 11:12:14
Associate 1 AVAILABLE 2/8/2006 11:27:46
Associate 1 2LUN 2/8/2006 11:27:48
Associate 1 AVAILABLE 2/8/2006 12:05:32
Associate 1 8WRP 2/8/2006 12:35:06
Associate 1 AVAILABLE 2/8/2006 13:06:47
Associate 1 4CST 2/8/2006 13:06:49
Associate 1 AVAILABLE 2/8/2006 13:09:33
Associate 1 9OTH 2/8/2006 13:29:06
Associate 1 AVAILABLE 2/8/2006 13:50:04
Associate 1 1BRK 2/8/2006 13:50:28
Associate 1 AVAILABLE 2/8/2006 14:06:38
Associate 1 LOGOFF 2/8/2006 15:28:44
Associate 1 UNAVAILABLE 2/8/2006 15:28:50
Associate 1 AVAILABLE 2/8/2006 15:31:50
Associate 1 LOGOFF 2/8/2006 15:31:54

 

[Bob Tarburton]

I need an extra column, (more like young Luke, but I don't see Yoda or Obi
Wan in a hurry to respond)

Note all formulas below assume that row 2 is the first row of data and row
5000 the last.
Adjust instances of $2 and/or $5000 to your range.

In call E2 add
=IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B500"),0)+ROW()),"",INDEX(D,MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B500"),0)+ROW()))-D2,"")
Copy down

In G1 type "Associate 1"
In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000))
In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000))
Both the MIN and MAX are array formulas, you must commit with
control+shift+enter
Now you can type in other Associates below in column G and copy H1:I1 down

Finding the next "AVAILABLE" after each "2LUN" doesn't seem to want to fit
inside the array Min or Max. I tested this minimally. If you have a problem,
please indicate the condition that caused failure.
One problem you might have is if the associate goes to lunch, gets tanked,
and comes back tomorrow. If you want to only count same day returns, use
this in E2 instead of the previous:
=IF(B2="2LUN",IF(ISERROR(MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B5000"),0)+ROW()),"",IF(INDEX(C:C,MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B5000"),0)+ROW())=C2,INDEX(D,MATCH("AVAILABLE",INDIRECT("B"&ROW()+1&":B5000"),0)+ROW())-D2,"")),"")
Good luck

 

[Guest]

I do nto see how this is matching to the associate. I tried it just like you
posted with no success - I also made a couple changes and still no success.

Any other suggestions?

I appreciate your help.

 

[Bob Tarburton]

Right, I didn't condider which Associate returned Available, just took the
first one.

In cell E2 add
=IF(B2="2LUN",IF(ISERROR(MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)>ROW(B2)),0),0)),"",INDEX(D$2$5000,MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)>ROW(B2)),0),0))-D2),"")
copy down

Or if you want to require same day return
=IF(B2="2LUN",IF(ISERROR(MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)>ROW(B2)),0),0)),"",IF(INDEX(C$2:C$5000,MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)>ROW(B2)),0),0))=C2,INDEX(D$2$5000,MATCH(1,INDEX((A$2:A$5000=A2)*(B$2:B$5000="AVAILABLE")*(ROW(B$2:B$5000)>ROW(B2)),0),0))-D2,"")),"")

Same MIN and MAX as before

Still leaving the beyond minimal testing to you
Sorry about that 1st one