0

I have two tables of data in Excel that were exported from our HR system. One contains estimated/planned workhours, the other contains actual worked hours. Each line has a date, hours worked, and the employee name. This is the same for both tables.

I've been asked to make an overview of staff workhours, by combining the actual worked hours with the estimated hours. That is to say: if hours were logged for that date, use the real hours table. If not, use the estimate table.

I have no idea where to start with this. I can make a pivottable, based on a table, that nicely shows the hours per date per employee, but I have no idea how to combine the two tables with the specific requirement to only include a row from table 2 if no corresponding row from table 1.

I cannot just merge them manually. It needs to be automatic so our software can spit out an overview regularly.

Edit: Since I've been asked to elaborate my clearly: I have one excel file containing two tables in separate worksheets. Both are data exports from our system. Both are indeed exported as tables, not just raw data. I control the export and can decide what columns to add/remove. Currently, both tables look like this:

enter image description here

As I explained, I need to combine the two into one pivottable that only counts the estimated hours when there are no actual hours for that employee on that date.

Improve this question
7
  • 1
    Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Feb 3, 2022 at 15:55
  • 1
    On the Data tab in Excel, there is a wonderful tool for solving just such tasks - Power Query. You can read a page on the Microsoft website dedicated to solving your particular problem (long, boring and not very clear) or watch a video YouTube (in 10 minutes you will know how to solve your problem in a dozen clicks)
    – JohnSUN
    Commented Feb 3, 2022 at 16:03
  • Does the HR system output two files or one file with two tables in it? Are they actual tables?
    – Engineer Toast
    Commented Feb 3, 2022 at 17:51
  • Also, do you only have one date row per employee in each file, or is there duplicates/updates/additions? Do all date & employee pairs always appear in both tables, or do you have say one row actual only where someone did not plan, one row planned where they didn't book actual and/or there are dates where some booked none or both, but other didn't (trying to think of all the permutations!).
    – ExcelEverything
    Commented Feb 3, 2022 at 18:39
  • @EngineerToast One file, two tables. Yes they are actual tables, not just data dumps. I can even control the name of the tables.
    – kiyojik557
    Commented Feb 4, 2022 at 9:52

1 Answer 1

Reset to default
1

Unfortunately, I cannot give a detailed answer with illustrations - screenshots from my localized version of Excel will not help you understand. So I suggested watching YouTube.

It's really simple:

  • create a query on the "real" table,
  • then create a query on the "assumed" table,
  • left join the first query on it,
  • expand the result,
  • filter the records with an empty field - you will get data for the records missing in the "real" table
  • delete the helper column,
  • append records from the first query,
  • sort (if needed) by date-name-project_number,
  • export the result into a new sheet in your workbook (really no need, Pivot Table can be created directly from query)

Based on the resulting table, create a pivot table. Save your workbook. The next time you open the workbook, just click Refresh All on the Data tab and get the result.

Improve this answer
1

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .