Grab First Login and Last Logoff if Employee Name and Date match

[Obrigado]

I tried to come up with a solution, but I am throwing in the towel after 3 hours. (how embarrassing) OK gurus and resident experts here is my Excel puzzle.

Details About The Excel File:
I have an Excel file with two sheets. Let’s call one DATA and two SUMMARY.

DATA Sheet.
DATA has been a range of values that has been converted into a table named INPUT.
INPUT has columns that contain the following information:

1. employee name abbreviations (“INPUT [ShortName]”) – values are text strings
2. login and logout information (“INPUT [AuthAction]”) – values are “login” or “logout”
3. the date the employee logged on or off (“INPUT [AuthDate]”) – values are dates
4. the time the employee logged off or on (“INPUT [AuthTime]”) – values are times of day

One example ShortName is CYNKLE.

SUMMARY Sheet.
SUMMARY has been a range of values that has been converted into a table named OUTPUT.
OUTPUT has columns that contain the following information:

1. one date for each day of the year, including weekends (“OUTPUT[Date]”) – values are dates
2. one column for each employee (“[OUTPUT[ShortName]”) – values are text strings

followed by

1. two columns for each employee that are named after the employee name abbreviation and end in either Login or Logout (e.g. “OUTPUT[CYNKLE_Login]”, “OUTPUT[CYNKLE_Logout]”)

Non-Excel Background:
On some days the employee being evaluated does not login or logout.
On some days the employee being evaluated logs in or logs out.
On most days employees are logging in and out multiple times throughout the day.
If an employee does not logout properly you will see no logout for that day, but they will have to login again the next day.
Thus,
If the first time entry for a day is a “login” I want to count it as a day to analyze.
If the last time entry for a day is a “logout” I want to count it as a day to analyze.

Summary of the Solution I am Needing Your Help With:
I would like to write a formula in OUTPUT[CYNKLE_Login] that matches the employee short name from INPUT[ShortName] to OUTPUT[ShortName] AND THEN matches the date from INPUT[AuthDate] to OUTPUT[AuthDate] and grabs the first login of the day in INPUT[AuthAction] and places the result in OUTPUT[CYNKLE_Login] if they worked that day. If they did not work that day the formula can return a zero value.

I would also like to write a formula in OUTPUT[CYNKLE_Logout] that matches the employee short name from INPUT[ShortName] to OUTPUT[ShortName] AND THEN matches the date from INPUT[AuthDate] to OUTPUT[AuthDate] and grabs the last login of the day INPUT[AuthAction] and places the result in OUTPUT[CYNKLE_Logoff] if they worked that day AND happened to logout. If they did not work that day, or forgot to logout that day, then the formula can return a zero value.

Thank You:
Thanks in advance for you help. I tried to be as clear as possible above to show that I respect your time. (Also, I am not advanced enough to jump into the VBA editor.)

 

[Matrix007]

Hi Can you please provide some sample data in a table format