Last Station Used

R

rsortilla

New Member
Joined
Aug 14, 2017
Messages
5
[TABLE="width: 678"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Name[/TD]
[TD]Workstation[/TD]
[TD]Login Date[/TD]
[TD]Login Time[/TD]
[TD]Logout Date[/TD]
[TD]Logout Time[/TD]
[/TR]
[TR]
[TD]ID00001[/TD]
[TD]Employee 1[/TD]
[TD]PC 1[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:30 AM[/TD]
[/TR]
[TR]
[TD]ID00002[/TD]
[TD]Employee 2[/TD]
[TD]PC 2[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]5:30 PM[/TD]
[/TR]
[TR]
[TD]ID00003[/TD]
[TD]Employee 3[/TD]
[TD]PC 3[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]11:00 AM[/TD]
[/TR]
[TR]
[TD]ID00001[/TD]
[TD]Employee 1[/TD]
[TD]PC 4[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:45 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]5:00 PM[/TD]
[/TR]
[TR]
[TD]ID00003[/TD]
[TD]Employee 3[/TD]
[TD]PC 5[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]11:03 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]7:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 263"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Employee ID[/TD]
[TD]Last Station Used[/TD]
[/TR]
[TR]
[TD]ID00001[/TD]
[TD] = What's the Formula?[/TD]
[/TR]
[TR]
[TD]ID00002[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I've been trying to solve this without using any VBA code to no avail. In this case, the employee can transfer to as many stations as he can.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
[TABLE="class: grid, width: 678"]
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee ID[/TD]
[TD]Name[/TD]
[TD]Workstation[/TD]
[TD]Login Date[/TD]
[TD]Login Time[/TD]
[TD]Logout Date[/TD]
[TD]Logout Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ID00001[/TD]
[TD]Employee 1[/TD]
[TD]PC 1[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:30 AM[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ID00002[/TD]
[TD]Employee 2[/TD]
[TD]PC 2[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]5:30 PM[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]ID00003[/TD]
[TD]Employee 3[/TD]
[TD]PC 3[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]11:00 AM[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]ID00001[/TD]
[TD]Employee 1[/TD]
[TD]PC 4[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]8:45 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]5:00 PM[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]ID00003[/TD]
[TD]Employee 3[/TD]
[TD]PC 5[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]11:03 AM[/TD]
[TD="align: right"]9/1/17[/TD]
[TD="align: right"]7:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the quick reply Jeff. I did try it and it seems to be not working on my end though.
 
Upvote 0
Here is what I get

Data Range
[Table="class: grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]
Employee ID​
[/td][td]
Name​
[/td][td]
Workstation​
[/td][td]
Login Date​
[/td][td]
Login Time​
[/td][td]
Logout Date​
[/td][td]
Logout Time​
[/td][/tr]

[tr][td]
2​
[/td][td]
ID00001​
[/td][td]
Employee 1​
[/td][td]
PC 1​
[/td][td]
9/1/2017​
[/td][td]
8:00 AM​
[/td][td]
9/1/2017​
[/td][td]
8:30 AM​
[/td][/tr]

[tr][td]
3​
[/td][td]
ID00002​
[/td][td]
Employee 2​
[/td][td]
PC 2​
[/td][td]
9/1/2017​
[/td][td]
8:30 AM​
[/td][td]
9/1/2017​
[/td][td]
5:30 PM​
[/td][/tr]

[tr][td]
4​
[/td][td]
ID00003​
[/td][td]
Employee 3​
[/td][td]
PC 3​
[/td][td]
9/1/2017​
[/td][td]
10:00 AM​
[/td][td]
9/1/2017​
[/td][td]
11:00 AM​
[/td][/tr]

[tr][td]
5​
[/td][td]
ID00001​
[/td][td]
Employee 1​
[/td][td]
PC 4​
[/td][td]
9/1/2017​
[/td][td]
8:45 AM​
[/td][td]
9/1/2017​
[/td][td]
5:00 PM​
[/td][/tr]

[tr][td]
6​
[/td][td]
ID00002​
[/td][td]
Employee 3​
[/td][td]
PC 5​
[/td][td]
9/1/2017​
[/td][td]
11:03 AM​
[/td][td]
9/1/2017​
[/td][td]
7:00 PM​
[/td][/tr]

[tr][td]
7​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
8​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
9​
[/td][td]
ID00001​
[/td][td]
PC 4​
[/td][td]
=LOOKUP(2,1/($A$2:$A$6=$A9),$C$2:$C$6)​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]

[tr][td]
10​
[/td][td]
ID00002​
[/td][td]
PC 5​
[/td][td]
=LOOKUP(2,1/($A$2:$A$6=$A10),$C$2:$C$6)​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]
[/table]
 
Upvote 0
Happy to help.

Click on the cell with the formula >> On the ribbon >> Formula >> Formula Auditing >> Evaluate Formula
 
Upvote 0
ExcelIsFun says that if we can solve it with LOOKUP, we can as well with INDEX, MATCH. Any alternative solution to this?
 
Upvote 0
The Lookup formula will be more efficient than an Index/Match so why do you want an alternative solution when the Lookup works :confused:
 
Upvote 0
Well you know, that Mel Gel Girvin is one smart fellow and I'm guessing he has a solution, but my brain is not coming up with one other than what I provided.

Maybe search his youtube channel for this solution. That is what I would have to do...
 
Upvote 0
Control+shift+enter, not just enter:

=INDEX($C$2:$C$6,MATCH(9.99999999999999E+307,IF($A$2:$A$6=$A9,$D$2:$D$6)))

is equivalent to

=LOOKUP(9.99999999999999E+307,1/($A$2:$A$6=$A9),$C$2:$C$6)

unless a blank instead of a date would correspond to the last occurrence of an employee id.

By the way, 2 behaves here as the more generic 9.99999999999999E+307 would.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Hours & minutes calculation
Replies
2
Views
155
Ron99
R
M
  • Solved
sumifs using date and time values
Replies
9
Views
243
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
430
myall_blues
myall_blues
A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
159
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
254
etaf
etaf

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

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
Back
Top