vlookup or sumif only first value and last value among ten different values

[chintu49]

Hi experts,

well, i was wondering if i can do a vlookup or sumif for the first and last value in entire column. suppose i have following data

Engg ID Login Time Logout Time
6900150 15:52 23:01
6900150 14:54 15:50
6900152 7:52 14:51
6900153 7:42 15:50
6900156 0:38 1:47
6900156 1:47 2:38
6900156 2:38 7:42
6900156 23:17 7:34
6900158 1:49 7:52
6900158 23:14 6:09
6900163 15:50 23:13
6900163 23:14 23:29
6900164 11:45 15:56
6900164 15:57 19:34
6900183 1:37 7:47
6900183 22:55 7:13
6900184 15:34 23:03
6900187 7:18 15:50
6900188 0:03 0:48
6900188 0:49 1:37
6900188 1:37 7:17
6900188 23:02 0:16
6900189 7:01 15:50
6900190 11:15 16:13
6900191 10:02 17:58
6900206 7:06 15:51
6900207 7:05 15:52
6900210 7:17 15:50
6900211 15:57 21:00
6900211 21:00 22:59
6900212 14:51 22:54
6900214 1:37 7:18
6900214 23:02 7:20


Now in the above data wht i wnt to do is that if i do a vlookup then it shud read the first login time for say engg id 6900156 and last logout time for the same and not the values in between. i am using sumif but then it is adding up all the login and logout time in their respective columns which doesnt help me to track wht time that agent was logged in and wht time did he logged out ??? please help me on this....

chintu........

[Domenic]

Try the following...

First entry:

=INDEX(B2:B34,MIN(IF(A2:A34=6900156,ROW(A2:A34)-CELL("row",A2)+1)))

...confirmed with CONTROL+SHIFT+ENTER.

Last entry:

=INDEX(C2:C34,MAX(IF(A2:A34=6900156,ROW(A2:A34)-CELL("row",A2)+1)))

...confirmed with CONTROL+SHIFT+ENTER.

OR

=LOOKUP(9.99999999999999E+307,1/(A2:A34=6900156),C2:C34)

...confirmed with just ENTER.

Hope this helps!

[chintu49]

hi domenic,

awesome, your reply and the answer was simply awesome, this will also help me...but this formula only reads the login and logout time of corresponding cells, i mean rows.....wht i want is suppose cell A1:A36 are the agent ids, in which few ids are repeated more than twice or thrice, and accordingly their different login and logout time will also change in column B1:B36 and C1:C36 respectively, so the look up or index formula should read the very first time that agent had logged in and last time he logged out of the system ? is this possible ?

thnx,

chintu........

[mangesh_yadav]

Suppose your data given in your example lies in the range A1:C33. A contains ids, B login times and C logout times.

Suppose E1 has an id you want to find the login and logout times. Then use the following formulae for the min login time and max logout times respectively.

=1/MAX((--($A$1:$A$33=E1)*(--($B$1:$B$33)<>0))*(1/$B$1:$B$33))

=MAX((--($A$1:$A$33=E1)*(--($C$1:$C$33)<>0))*($C$1:$C$33))

After entering the formula, press Control+Shift+Enter, as these are array formulae.

e.g.
for id 6900150, the min login time returned by the above formula is 14:54, and max logout time is 23:01


- Mangesh