summarize (pivot or otherwise) sales in region by salespersons when regional responsibilities change over months

[chintugudda]

Hi
Below is the excel file. I am looking to get a pivot or summarize the result. The sales data is by month and region. Salespersons are identified in a mapping table. Trying to find an easy way to summarize. In real life example, regions are not 4 but variable is 100s. So dont want to use manual formulas. Any ideas?

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Region[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Total[/TD]
[TD]Jan-Sales Person[/TD]
[TD="colspan: 2"]Feb-Salesperson[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]East[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]220[/TD]
[TD]Rick[/TD]
[TD="colspan: 2"]Rick 50% Boss 50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]West[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]402[/TD]
[TD]Tom[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]North[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]116[/TD]
[TD]John[/TD]
[TD]Boss[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]South[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]320[/TD]
[TD]Boss[/TD]
[TD]Jeb[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]508[/TD]
[TD="align: right"]550[/TD]
[TD="align: right"]1058[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="colspan: 2"]DESIRED OUTPUT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Rick[/TD]
[TD]Tom[/TD]
[TD]John[/TD]
[TD]Boss[/TD]
[TD]Jeb[/TD]
[TD]Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD]East[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD]West[/TD]
[TD][/TD]
[TD="align: right"]202[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]202[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]256[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD]South[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]440[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]256[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]1058[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[soadfan]

Mark data, insert pivotTable, change display to classic pivot layout, change summarize of values columns as desired

 

[chintugudda]

Hi
I need the summaries by persons. FOr example, you will see John having 2 different regions in 2 months. Also, Boss has a 50% share in 2 nd month. Normally, i could simply do an excel file with manual formula's for each month. I am trying to see if there is a better solution around tha, where idont have keep doing it every month. Would consolidate multiple spreadsheet work?

 

[soadfan]

Well, to be honest i have a lot of experience as sales KAM and Supervisor. I've worked with tons of reports called STT (sales to trade).
Pivot is pretty powerful tool to manage/analyze data. In my opinion it should do the work. If it's too complex for Pivot, you should give a shot of Power Bi.