Combining two data ranges for use in a pivot table

[AustinLe]

Hello,

I would like to find a way to get one set of data that is one size to work with another set of data that is another size with the same slicer. I have a "Week" column that i want to use as the slicer. each data set has the same week number, but different amounts of data that correspond to that week. My goal is to make a summary pivot table that is easy to sort.

If you look at my sample file everything will be made much more clear, thanks!

Combine Dif Sized Ranges Pivot.xlsx

[Melvinrobb]

You have Joe listed twice for week 1, both in the "Hours Needed" table and the "Available" table. The desired output shows both entries summarized separately. That is what you want? If an individual is listed twice in the "Hours Needed" table, will they always have two entries in the "Available" table? would you ever have an individual listed twice in the "Available" table, but not in the "Hours Needed" Table?

[AustinLe]

Hello Melvinrobb,

I made a mistake in adding Joe twice in the available table, but any worker can be included any number of times in the "Hours Needed" table because there could be multiple tasks they need to complete in a week. The "Available" table is a manual input field that is a guess of a workers total hours for that week. Those two differences are what stump the pivot table the way it is setup right now. An individual should not be listed twice in the "Available" table or in the summary pivot table (desired output). I realize this a clunky way of inputting the data but it's the way everyone is used to doing it.. :/

I'm okay with using helper columns or another solution as long as the summary pivot can sort by week for both the SUM of hours needed (by that week per person) and available hours.

P.S. the percent allocated would be a calculated field of Sum of Hours Needed / Available hours. Again by week.

Hope this clears your questions up. Thank you for the reply and the help so far!

-Austin

[Melvinrobb]

That small mistake is a major one in terms of a suggested solution.
Assuming the "Available" table has one row for every person for every week, you don't even need a pivot table.
You need to have ONE table for the "Available" numbers, with a column signifying what week each entry is for. Having each week in a separate table will just not work.
From there, the formula I used for the two added columns will give you the summary you are looking for.

[AustinLe]

This works, thank you!

[Melvinrobb]

While a pivot table is not necessary, making one so you could summarize all of the weeks would be useful.
A calculated field may be the right way, but finding an average of the percentages may actually be better.
Take a look at the scenario below:

Joe - Week 1 - 4 Available - 4 Needed
Joe - Week 2 - 8 Available - 0 Needed

If you use a calculated field to find the total % for Joe, you would get 4/12 = 33%.
But if you found an average of the weekly %, you would get (100% + 0%)/2 = 50%

Not sure which one makes more sense for your purposes, but both are options. And IMO, 50% is a better summary of the average % allocation week over week. He had 100% allocation in week 1, and 0% allocation in week 2, so 50% is his average, not 33%.