Last Station Used

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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
[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

Forum statistics

Threads
1,224,825
Messages
6,181,190
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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