VLOOKUP business case

[ziak]

Hi - I need help with an issue and it's best to give some background
information first....please read and bear with me!

I am addressing a compliance issue with time keeping. Each week, employees
in my company must submit timesheets before a cut off time on Friday. In
turn, their managers must approve them before a cut off time on Monday. If
they are not submitted and approved on time, the labor hours do not count in
our weekly numbers...and we get hell from HQ.

Due to an exceptionally high volume of "missing timesheets," we are
monitoring two things: employees who don't submit on time, and subsequently,
managers who don't approve on time.

Each week, I get a list each of the non-submitters and a separate list of
the non-approvers. Identifying the non-submitters is easy - it's those on
the non-submitter list. However, I need to distinguish between the managers
who were tardy in approving their timesheets from those who did not have
timesheets to approve (ie, their subodinates did not submit one on time).

Here is what the non-submitter report looks like:

A B C
Emp_ID As of_Date As of_Time
1 123456 05-01-07 5pm
2 456789 05-01-07 5pm
3 123456 05-08-07 5pm

Here is what the non-approver report looks like:

A B C D
Emp_ID As of_Date As of_Time Approver_ID
1 123456 05-01-07 5pm 987654
2 234567 05-01-07 5pm 987654
3 456789 05-01-07 5pm 987654
4 123456 05-08-07 5pm 987654

From this simplified case, I can tell that Manager 987654 was late in
approving timesheets for the first week since 234567 submitted on time (ie,
was not on the non-submitter list). For the second week, however, Manager
987654 did approve her timesheets on time, but Employee 123456 did not submit
on time. The manager should be penalized for three timesheets instead of
four.

I am sorry for making this into a "business case," but I had to explain the
issue...to top it off, I have a call to explain this at 2pm EST tomorrow.

I can do a VLOOKUP, but how to do it where it looks first for the date and
time, and then matches to the Emp IDs?

Any help or questions are GREATLY appreciated!

P.S. - I have a pivot table with the views that I want, and including any
other items in it would render it way too busy, and quite frankly, useless
for what I want to achieve.

 

[AKphidelt]

Alright, i'm not quite understanding your case. From the way it's explained
it looks like the Manager should be responsible for 1 late approval, because
3 of his 4 late approvals were non-submittals? Am I totally off track, or
what am I missing?

But a formula to check if they are submitted, you can put a formula in
Column E next to the non-approval side and put the formula in E1 of the
approver.

=IF(SUMPRODUCT((SubmitterA1:SubmitterA3=ApproverA1)*(SubmitterB1:SubmitterB3=ApproverB1))>0,"Not Submitted","Submitted")

What this says is if both the employee ID and the Date match from the
submitted sheet to the approved sheet, then the timesheet has not been
submitted, if it is on the approvers sheet but not the submitters sheet, it
lists it as submitted.

Let me know if Im on track at all

 

[ziak]

Thanks for replying. You are right, the scenario was not correct. Assume
the following:

Manager 987654 has the following direct reports
Emp 123456
Emp 234567
Emp 345678
Emp 456789

'unsubmitted' report shows
A B C
Emp_ID As of_Date As of_Time
1 123456 05-01-07 5pm
2 456789 05-01-07 5pm
3 123456 05-08-07 5pm

'unapproved' report shows
A B C D

Emp_ID As of_Date As of_Time Approver_ID
1 123456 05-01-07 5pm 987654
2 234567 05-01-07 5pm 987654
3 345678 05-01-07 5pm 987654
4 456789 05-01-07 5pm 987654
5 123456 05-08-07 5pm 987654

The manager would get two penalties for 5-1-07 (234567 and 345678 submitted
on time), but none for 5-8-07 (123456 did not submit on time).

The formula you provided yields "Submitted" only and I know that is not the
case. In other words, the SUMPRODUCT is "0" for each instance. Below is the
actual formula typed in E1 of the 'unapproved' worksheet.

=IF(SUMPRODUCT((unsubmitted!A2:A486=unapproved!A2)*(unsubmitted!B2:B486=unapproved!B2))>0,"Not Submitted","Submitted")

One thing I forgot to mention is that the employee ID fields are
alpha-numeric as well as numeric, and in some cases were converted to text to
generate additional reports. Would that have anything to do with it?

In the interest of time, I am going to seperate the data into partial
worksheets by week, do a simple VLOOKUP, and then summarize the results.
This will be an ongoing process so if you can assist further, I would greatly
appreciate it.

Thanks in advance.