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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

B
  • Solved
Possible to pull apart 1st section of string for date & time
2 3
Replies
23
Views
450
Peter_SSs
Peter_SSs
R
  • Solved
Hours & minutes calculation
Replies
2
Views
175
Ron99
R
M
  • Solved
sumifs using date and time values
Replies
9
Views
327
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Get enddatetime if X no. of working hours is added to startdatetime
Replies
1
Views
438
myall_blues
myall_blues
A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
164
Asbestos_Jen
Asbestos_Jen

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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