Pivot multiple ranges or combine in Data Model

[megera716]

I have a spreadsheet with a tab for each month. Each of these tabs contains the same 11 columns, except F and G contain the month name so those vary.

I want to pivot on the Admin name, with the ability to expand and see the breakdown by RD under each Admin, by month. Admins are occasionally reassigned so I want to see any RD they were working with. I first tried doing a multiple range pivot but because it lacks the same filter-ability of a single range pivot, it's not working (I couldn't have admin on the left, with RD breakdown, with months across the top). I have been reading about the Data Model, relationships, and maybe it just won't work with my set of data. When I was trying to create the relationships, I thought I should do it off the Admin column because that was the primary key I was wanting to connect across tabs, but it kept throwing errors because the Admin column has duplicate values.

For example, November numbers:
[TABLE="width: 444"]
<tbody>[TR]
[TD]Admin[/TD]
[TD]RD[/TD]
[TD]Team[/TD]
[TD]Nov Target[/TD]
[TD]Nov Totals[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD]Adam C[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD]Aaron[/TD]
[TD]Clinical[/TD]
[TD]20[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Brandy[/TD]
[TD]Monique[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Kristen[/TD]
[TD]Erik[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Kristen[/TD]
[TD]Paul[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]McKenna[/TD]
[TD]Ben[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]McKenna[/TD]
[TD]Emily[/TD]
[TD]Clinical[/TD]
[TD]12[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]McKenna[/TD]
[TD]Mark[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Ann[/TD]
[TD]Paul P[/TD]
[TD]Financial[/TD]
[TD]20[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Ann[/TD]
[TD]Joe[/TD]
[TD]Financial[/TD]
[TD]16[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]



But in October (on a separate tab):
[TABLE="width: 472"]
<tbody>[TR]
[TD]Admin[/TD]
[TD]RD[/TD]
[TD]Team[/TD]
[TD]Oct Target[/TD]
[TD]Oct Totals[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD]Garrett[/TD]
[TD]Clinical[/TD]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD]Aaron[/TD]
[TD]Clinical[/TD]
[TD]5[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Brandy[/TD]
[TD]Monique[/TD]
[TD]Clinical[/TD]
[TD]20[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]Brandy[/TD]
[TD]Adam C[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]Kristen[/TD]
[TD]Erik[/TD]
[TD]Clinical[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Kristen[/TD]
[TD]Paul[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]McKenna[/TD]
[TD]Ben[/TD]
[TD]Clinical[/TD]
[TD]16[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


So the Pivot would look like this:
[TABLE="width: 554"]
<tbody>[TR]
[TD]ADMIN[/TD]
[TD]RD[/TD]
[TD]Dec Target[/TD]
[TD]Dec Totals[/TD]
[TD]Nov Target[/TD]
[TD]Nov Totals[/TD]
[TD]Oct Target[/TD]
[TD]Oct Totals[/TD]
[/TR]
[TR]
[TD]Rebecca[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Aaron[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Adam C[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Garrett[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Brandy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Adam C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Monique[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[/TR]
</tbody>[/TABLE]


Hopefully this makes sense. Sorry if it's not entirely clear, obviously I don't want to paste in ALL the data for anonymity, but I'm happy to answer questions and clarify!

 

[megera716]

bump

 

[Jerry Sullivan]

Hi megara716,

It will be easier to manipulate the multiple data sources if they have the exact same field names. I'd suggest you rename the two fields to "Target" and "Totals", then add an additional field for "Month". Your revised November sheet might look like this...

Excel2016
	A	B	C	D	E	F
1	Admin	RD	Team	Target	Totals	Month
2	Rebecca	Adam C	Clinical	16	10	Nov
3	Rebecca	Aaron	Clinical	20	24	Nov
4	Brandy	Monique	Clinical	16	12	Nov
5	Kristen	Erik	Clinical	16	6	Nov
6	Kristen	Paul	Clinical	16	9	Nov
7	McKenna	Ben	Clinical	16	9	Nov
8	McKenna	Emily	Clinical	12	3	Nov
9	McKenna	Mark	Clinical	16	10	Nov
10	Ann	Paul P	Financial	20	14	Nov
11	Ann	Joe	Financial	16	10	Nov
Nov

A primary key isn't needed for the operation that you describe since the query is Union type (appending the tables) instead of a Join (lookup/merge) type.

If you have Power Query installed, you can use that make the query to join all the tables into a single table in the Data Model. Once you have that you can get the PivotTable that you wanted by placing the Target and Total fields in the Values area, and placing the Month in the Columns area of the PivotTable.

The end result would look like this (you can rename the "Sum of.." fields-I wanted to show the default names that appear when you create the Pivot).

Excel2016
	A	B	C	D	E	F	G
3		Column Labels
4		Nov		Dec		Total Sum of Target	Total Sum of Totals
5	Row Labels	Sum of Target	Sum of Totals	Sum of Target	Sum of Totals
6	Aaron	20	24	20	24	40	48
7	Adam C	16	10	16	10	32	20
8	Ben	16	9	16	9	32	18
9	Emily	12	3	12	3	24	6
10	Erik	16	6	16	6	32	12
11	Joe	16	10	16	10	32	20
12	Mark	16	10	16	10	32	20
13	Monique	16	12	16	12	32	24
14	Paul	16	9	16	9	32	18
15	Paul P	20	14	20	14	40	28
16	Grand Total	164	107	164	107	328	214
Pivot

 

[megera716]

Hi megara716,

It will be easier to manipulate the multiple data sources if they have the exact same field names. I'd suggest you rename the two fields to "Target" and "Totals", then add an additional field for "Month". Your revised November sheet might look like this...

Excel2016

	A	B	C	D	E	F
Admin	RD	Team	Target	Totals	Month
Rebecca	Adam C	Clinical	Nov
Rebecca	Aaron	Clinical	Nov
Brandy	Monique	Clinical	Nov
Kristen	Erik	Clinical	Nov
Kristen	Paul	Clinical	Nov
McKenna	Ben	Clinical	Nov
McKenna	Emily	Clinical	Nov
McKenna	Mark	Clinical	Nov
Ann	Paul P	Financial	Nov
Ann	Joe	Financial	Nov

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]

</tbody>

Nov​

A primary key isn't needed for the operation that you describe since the query is Union type (appending the tables) instead of a Join (lookup/merge) type.

If you have Power Query installed, you can use that make the query to join all the tables into a single table in the Data Model. Once you have that you can get the PivotTable that you wanted by placing the Target and Total fields in the Values area, and placing the Month in the Columns area of the PivotTable.

The end result would look like this (you can rename the "Sum of.." fields-I wanted to show the default names that appear when you create the Pivot).
Excel2016

	A	B	C	D	E	F	G
Column Labels
Nov	Dec	Total Sum of Target	Total Sum of Totals
Row Labels	Sum of Target	Sum of Totals	Sum of Target	Sum of Totals
Aaron
Adam C
Ben
Emily
Erik
Joe
Mark
Monique
Paul
Paul P
Grand Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]48[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]24[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]20[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]28[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]164[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]164[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]328[/TD]
[TD="align: right"]214[/TD]

</tbody>

Pivot​

Thanks Jerry. I agree a single table would be easier. I'm helping a coworker and her data is existing in the separate tabs so I was trying to make that layout work, but after continuing to beat my head against the wall last night, I think you and I are right that it would just be easiest to combine the data.