software

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 timeLunch DurationLast 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:54I 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=quot;2LUNquot;,IF(ISERROR(MATCH(quot;AVAILABLEquot;,INDIREC T(quot;Bquot;amp;ROW() 1amp;quot;:B500quot;),0) ROW()),quot;quot;,INDEX(D,MATC H(quot;AVAILABLEquot;,INDIRECT(quot;Bquot;amp;ROW() 1amp;quot;:B500quot;),0) ROW ()))-D2,quot;quot;)
Copy down

In G1 type quot;Associate 1quot;
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 quot;AVAILABLEquot; after each quot;2LUNquot; 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=quot;2LUNquot;,IF(ISERROR(MATCH(quot;AVAILABLEquot;,INDIREC T(quot;Bquot;amp;ROW() 1amp;quot;:B5000quot;),0) ROW()),quot;quot;,IF(INDEX(C:C, MATCH(quot;AVAILABLEquot;,INDIRECT(quot;Bquot;amp;ROW() 1amp;quot;:B5000quot;),0 ) ROW())=C2,INDEX(D,MATCH(quot;AVAILABLEquot;,INDIRECT(quot; Bquot;amp;ROW() 1amp;quot;:B5000quot;),0) ROW())-D2,quot;quot;)),quot;quot;)
Good luckquot;JRquot; gt; wrote in message
...
gt; Hello Excel Yoda's
gt;
gt; For me this is an impossibility, but I know for some of you the force will
gt; guide you to the answer. The first table shows my data. Please keep in
gt; mind
gt; that immediately following Associate 1 there will be associate 2,
gt; Associate
gt; 3, etc.
gt;
gt; Here is how I would like to return the data;
gt; A B C
gt; D
gt; Associate first logon time Lunch Duration Last Logoff time
gt;
gt; The associate name will already be listed in the A: column, so the times
gt; will likely need to match the A: entry. The total lunch time can be
gt; calculated by taking the entry marked quot;2LUNquot; and subtracting that time
gt; from
gt; the first quot;Availablequot; time following the quot;2LUNquot;
gt;
gt; Bottom line is I do not know how to write min/max to match a criteria with
gt; a
gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; appreciate your effort.
gt;
gt; A B C D
gt; Name Event Type Date Start Time
gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; Associate 1 4CST 2/8/2006 13:06:49
gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt;
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.

quot;Bob Tarburtonquot; wrote:

gt; I need an extra column, (more like young Luke, but I don't see Yoda or Obi
gt; Wan in a hurry to respond)
gt;
gt; Note all formulas below assume that row 2 is the first row of data and row
gt; 5000 the last.
gt; Adjust instances of $2 and/or $5000 to your range.
gt;
gt; In call E2 add
gt; =IF(B2=quot;2LUNquot;,IF(ISERROR(MATCH(quot;AVAILABLEquot;,INDIREC T(quot;Bquot;amp;ROW() 1amp;quot;:B500quot;),0) ROW()),quot;quot;,INDEX(D,MATC H(quot;AVAILABLEquot;,INDIRECT(quot;Bquot;amp;ROW() 1amp;quot;:B500quot;),0) ROW ()))-D2,quot;quot;)
gt; Copy down
gt;
gt; In G1 type quot;Associate 1quot;
gt; In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000))
gt; In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000))
gt; Both the MIN and MAX are array formulas, you must commit with
gt; control shift enter
gt; Now you can type in other Associates below in column G and copy H1:I1 down
gt;
gt; Finding the next quot;AVAILABLEquot; after each quot;2LUNquot; doesn't seem to want to fit
gt; inside the array Min or Max. I tested this minimally. If you have a problem,
gt; please indicate the condition that caused failure.
gt; One problem you might have is if the associate goes to lunch, gets tanked,
gt; and comes back tomorrow. If you want to only count same day returns, use
gt; this in E2 instead of the previous:
gt; =IF(B2=quot;2LUNquot;,IF(ISERROR(MATCH(quot;AVAILABLEquot;,INDIREC T(quot;Bquot;amp;ROW() 1amp;quot;:B5000quot;),0) ROW()),quot;quot;,IF(INDEX(C:C, MATCH(quot;AVAILABLEquot;,INDIRECT(quot;Bquot;amp;ROW() 1amp;quot;:B5000quot;),0 ) ROW())=C2,INDEX(D,MATCH(quot;AVAILABLEquot;,INDIRECT(quot; Bquot;amp;ROW() 1amp;quot;:B5000quot;),0) ROW())-D2,quot;quot;)),quot;quot;)
gt; Good luck
gt;
gt;
gt; quot;JRquot; gt; wrote in message
gt; ...
gt; gt; Hello Excel Yoda's
gt; gt;
gt; gt; For me this is an impossibility, but I know for some of you the force will
gt; gt; guide you to the answer. The first table shows my data. Please keep in
gt; gt; mind
gt; gt; that immediately following Associate 1 there will be associate 2,
gt; gt; Associate
gt; gt; 3, etc.
gt; gt;
gt; gt; Here is how I would like to return the data;
gt; gt; A B C
gt; gt; D
gt; gt; Associate first logon time Lunch Duration Last Logoff time
gt; gt;
gt; gt; The associate name will already be listed in the A: column, so the times
gt; gt; will likely need to match the A: entry. The total lunch time can be
gt; gt; calculated by taking the entry marked quot;2LUNquot; and subtracting that time
gt; gt; from
gt; gt; the first quot;Availablequot; time following the quot;2LUNquot;
gt; gt;
gt; gt; Bottom line is I do not know how to write min/max to match a criteria with
gt; gt; a
gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt; gt; appreciate your effort.
gt; gt;
gt; gt; A B C D
gt; gt; Name Event Type Date Start Time
gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt; gt;
gt;
gt;
gt;

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

In cell E2 add
=IF(B2=quot;2LUNquot;,IF(ISERROR(MATCH(1,INDEX((A$2:A$5000 =A2)*(B$2:B$5000=quot;AVAILABLEquot;)*(ROW(B$2:B$5000)gt;ROW (B2)),0),0)),quot;quot;,INDEX(D$2$5000,MATCH(1,INDEX((A$ 2:A$5000=A2)*(B$2:B$5000=quot;AVAILABLEquot;)*(ROW(B$2:B$5 000)gt;ROW(B2)),0),0))-D2),quot;quot;)
copy down

Or if you want to require same day return
=IF(B2=quot;2LUNquot;,IF(ISERROR(MATCH(1,INDEX((A$2:A$5000 =A2)*(B$2:B$5000=quot;AVAILABLEquot;)*(ROW(B$2:B$5000)gt;ROW (B2)),0),0)),quot;quot;,IF(INDEX(C$2:C$5000,MATCH(1,INDEX( (A$2:A$5000=A2)*(B$2:B$5000=quot;AVAILABLEquot;)*(ROW(B$2: B$5000)gt;ROW(B2)),0),0))=C2,INDEX(D$2$5000,MATCH( 1,INDEX((A$2:A$5000=A2)*(B$2:B$5000=quot;AVAILABLEquot;)*( ROW(B$2:B$5000)gt;ROW(B2)),0),0))-D2,quot;quot;)),quot;quot;)

Same MIN and MAX as before

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

quot;JRquot; gt; wrote in message
...
gt;I do nto see how this is matching to the associate. I tried it just like
gt;you
gt; posted with no success - I also made a couple changes and still no
gt; success.
gt;
gt; Any other suggestions?
gt;
gt; I appreciate your help.
gt;
gt; quot;Bob Tarburtonquot; wrote:
gt;
gt;gt; I need an extra column, (more like young Luke, but I don't see Yoda or
gt;gt; Obi
gt;gt; Wan in a hurry to respond)
gt;gt;
gt;gt; Note all formulas below assume that row 2 is the first row of data and
gt;gt; row
gt;gt; 5000 the last.
gt;gt; Adjust instances of $2 and/or $5000 to your range.
gt;gt;
gt;gt; In call E2 add
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
gt;gt; Copy down
gt;gt;
gt;gt; In G1 type quot;Associate 1quot;
gt;gt; In H1 =MIN(IF(A$2:A$5000=G1,E$2:E$5000))
gt;gt; In I1 =MAX(IF(A$2:A$5000=G1,E$2:E$5000))
gt;gt; Both the MIN and MAX are array formulas, you must commit with
gt;gt; control shift enter
gt;gt; Now you can type in other Associates below in column G and copy H1:I1
gt;gt; down
gt;gt;
gt;gt; Finding the next quot;AVAILABLEquot; after each quot;2LUNquot; doesn't seem to want to
gt;gt; fit
gt;gt; inside the array Min or Max. I tested this minimally. If you have a
gt;gt; problem,
gt;gt; please indicate the condition that caused failure.
gt;gt; One problem you might have is if the associate goes to lunch, gets
gt;gt; tanked,
gt;gt; and comes back tomorrow. If you want to only count same day returns, use
gt;gt; this in E2 instead of the previous:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
gt;gt; Good luck
gt;gt;
gt;gt;
gt;gt; quot;JRquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello Excel Yoda's
gt;gt; gt;
gt;gt; gt; For me this is an impossibility, but I know for some of you the force
gt;gt; gt; will
gt;gt; gt; guide you to the answer. The first table shows my data. Please keep
gt;gt; gt; in
gt;gt; gt; mind
gt;gt; gt; that immediately following Associate 1 there will be associate 2,
gt;gt; gt; Associate
gt;gt; gt; 3, etc.
gt;gt; gt;
gt;gt; gt; Here is how I would like to return the data;
gt;gt; gt; A B C
gt;gt; gt; D
gt;gt; gt; Associate first logon time Lunch Duration Last Logoff time
gt;gt; gt;
gt;gt; gt; The associate name will already be listed in the A: column, so the
gt;gt; gt; times
gt;gt; gt; will likely need to match the A: entry. The total lunch time can be
gt;gt; gt; calculated by taking the entry marked quot;2LUNquot; and subtracting that time
gt;gt; gt; from
gt;gt; gt; the first quot;Availablequot; time following the quot;2LUNquot;
gt;gt; gt;
gt;gt; gt; Bottom line is I do not know how to write min/max to match a criteria
gt;gt; gt; with
gt;gt; gt; a
gt;gt; gt; sumproduct. I know I ask a lot, but I really could use the help and
gt;gt; gt; appreciate your effort.
gt;gt; gt;
gt;gt; gt; A B C D
gt;gt; gt; Name Event Type Date Start Time
gt;gt; gt; Associate 1 LOGON 2/8/2006 6:58:54
gt;gt; gt; Associate 1 LOGOFF 2/8/2006 6:59:03
gt;gt; gt; Associate 1 LOGON 2/8/2006 6:59:06
gt;gt; gt; Associate 1 LOGON 2/8/2006 6:59:07
gt;gt; gt; Associate 1 LOGON 2/8/2006 6:59:14
gt;gt; gt; Associate 1 8WRP 2/8/2006 8:41:20
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 8:43:46
gt;gt; gt; Associate 1 1BRK 2/8/2006 9:27:51
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 9:40:25
gt;gt; gt; Associate 1 8WRP 2/8/2006 10:05:09
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 10:07:28
gt;gt; gt; Associate 1 8WRP 2/8/2006 10:20:32
gt;gt; gt; Associate 1 AVAILABLE 2/8/200 10:27:58
gt;gt; gt; Associate 1 8WRP 2/8/2006 11:12:14
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 11:27:46
gt;gt; gt; Associate 1 2LUN 2/8/2006 11:27:48
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 12:05:32
gt;gt; gt; Associate 1 8WRP 2/8/2006 12:35:06
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 13:06:47
gt;gt; gt; Associate 1 4CST 2/8/2006 13:06:49
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 13:09:33
gt;gt; gt; Associate 1 9OTH 2/8/2006 13:29:06
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 13:50:04
gt;gt; gt; Associate 1 1BRK 2/8/2006 13:50:28
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 14:06:38
gt;gt; gt; Associate 1 LOGOFF 2/8/2006 15:28:44
gt;gt; gt; Associate 1 UNAVAILABLE 2/8/2006 15:28:50
gt;gt; gt; Associate 1 AVAILABLE 2/8/2006 15:31:50
gt;gt; gt; Associate 1 LOGOFF 2/8/2006 15:31:54
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;