Report to compare lists of hours worked

[sleeman]

Hi all, I hope you can help with this.

Our firm has multiple projects running at the same time (let's say about 100 projects) with a staff of about 25. Every Monday we plan the week's work by attributing employee hours to the various projects. Not all projects are worked on every week. After the week is over, we receive a report containing the actual projet hours, by employee.

I want to make a report that compares actual hours to hours planned, organized by project. The attached file gives an exemple of what I mean. I'm pretty good with pivot tables, but I'm stumped as to how to easily create a third table from the first two.

Thank you so much for any help!
Vince

[noboffinme]

Hi sleeman,

You can consolidate the 2 sources into 1 Pivot Table using the below method.
https://www.youtube.com/watch?v=wdemm8LzzVY

[sleeman]

Hi noboffinme, Thanks for the info. I'm new to multiple table pivot tables, so I tried, but I'm having trouble getting it to work. The problem may be that since I don't have a unique primary key. Can it still work?

[noboffinme]

Try this link, it will use the Employee as your PK and as the Page field in the Pivot.

https://www.youtube.com/watch?v=wdemm8LzzVY

[sleeman]

That`s the same video. Did you mean to link to a different one?

[noboffinme]

Hi sleeman,

Sorry, yes that's the wrong link & I was also getting the duplicates error.

So, I've tried another method using VLOOKUP.

You will need to create a new column that has the Employee No & Project No together.

Do the same with your 2 tables and use the VLOOKUP to bring them together.

I've created this on sheet2 of your file - attached.

Not as good as an included feature but it does work.

Cheers

[sleeman]

Yes that's very good. By combining Employee & Project, they become unique keys. I worked at that solution for a while , but the difficulty is creating the combined list (your column K). How did you do it? I think I'll have to write a VBA macro to get all unique Employee&Project values from both lists. I don't see any way around it. I was hoping there would be a solution through tables pivot tables! Thanks for your help.

[noboffinme]

Have a look at the Formula in Cols 'A' & 'F', it's simply 'A3&B3'. You can also do this across worksheets or wherever.