Creating a Pivot Table from rows of data

[andybrown115]

I struggled to word the title of this thread properly... so here goes the explanation.

I have a set of system-exported data that shows me how much margin/profit we make on a particular timesheet that comes in from some of our contractors. The deals that are done are split between our sales consultants in our timesheet system. so a timesheet that makes us £100 may be split 70/30 between two consultants - so one consultant would have £70 with the other getting £30 against their target for the week.

What I am struggling with is the way the data comes out and then populating that into a pivot table. This is what comes out:

[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[TH]

F​

[/TH]
[TH]

G​

[/TH]
[TH]

H​

[/TH]
[TH]

I​

[/TH]
[TH]

J​

[/TH]
[TH]

K​

[/TH]
[TH]

L​

[/TH]
[TH]

M​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

1​

[/TD]
[TD]Contractor[/TD]
[TD]Margin[/TD]
[TD]CON1[/TD]
[TD]CON2[/TD]
[TD]CON3[/TD]
[TD]PERC1[/TD]
[TD]PERC2[/TD]
[TD]PERC3[/TD]
[TD]SPLIT1[/TD]
[TD]SPLIT2[/TD]
[TD]SPLIT3[/TD]
[TD]Invoice Week[/TD]
[TD]Timesheet Week[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

2​

[/TD]
[TD]Bloggs, Joe[/TD]
[TD]

105.69​

[/TD]
[TD]

KG​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

105.69​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

07/01/2018​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

3​

[/TD]
[TD]Wiggins, Bradley[/TD]
[TD]

204.50​

[/TD]
[TD]

KG​

[/TD]
[TD]

RA​

[/TD]
[TD][/TD]
[TD]

70​

[/TD]
[TD]

30​

[/TD]
[TD][/TD]
[TD]

143.15​

[/TD]
[TD]

61.35​

[/TD]
[TD]

-​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

14/01/2018​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

4​

[/TD]
[TD]Knightly, Kiera[/TD]
[TD]

199.00​

[/TD]
[TD]

RA​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

100​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

199.00​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

21/01/2018​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

5​

[/TD]
[TD]Jones, Dave[/TD]
[TD]

203.00​

[/TD]
[TD]

RA​

[/TD]
[TD]

KG​

[/TD]
[TD]

QSW​

[/TD]
[TD]

50​

[/TD]
[TD]

15​

[/TD]
[TD]

35​

[/TD]
[TD]

101.50​

[/TD]
[TD]

30.45​

[/TD]
[TD]

71.05​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]

07/01/2018​

[/TD]
[/TR]
</tbody>[/TABLE]

So Bradley's timesheet value is £204.50 split 70/30 between consultants KG and RA respectively.

I'd not have an issue pivoting that to show the data if each split was shown on a separate row. But as it's across columns I'm a little baffled. I can neither think of a good formula to append to the data table nor a way to arrange the pivot table.

'I want' the pivot table to look like the below. The split margin is the sum of each consultant's initials against the SPLIT1 SPLIT2 or SPLIT3 as appropriate.

PIVOT TABLE
[TABLE="class: head"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]

A​

[/TH]
[TH]

B​

[/TH]
[TH]

C​

[/TH]
[TH]

D​

[/TH]
[TH]

E​

[/TH]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

7​

[/TD]
[TD]Invoice Week[/TD]
[TD]Consultant[/TD]
[TD]

Contractor​

[/TD]
[TD]Timesheet Week[/TD]
[TD]Split Margin[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

8​

[/TD]
[TD]

04/03/2018​

[/TD]
[TD]KG[/TD]
[TD]Bloggs, Joe[/TD]
[TD]

07/01/2018​

[/TD]
[TD]

105.69​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Wiggins, Bradley[/TD]
[TD]

14/01/2018​

[/TD]
[TD]

143.15​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones, Dave[/TD]
[TD]

07/01/2018​

[/TD]
[TD]

30.45​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

11​

[/TD]
[TD][/TD]
[TD]RA[/TD]
[TD]Wiggins, Bradley[/TD]
[TD]

14/01/2018​

[/TD]
[TD]

61.35​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

12​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Knightly, Kiera[/TD]
[TD]

21/01/2018​

[/TD]
[TD]

199.00​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones, Dave[/TD]
[TD]

07/01/2018​

[/TD]
[TD]

101.50​

[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E0E0F0"]

14​

[/TD]
[TD][/TD]
[TD]QSW[/TD]
[TD]Jones, Dave[/TD]
[TD]

07/01/2018​

[/TD]
[TD]

71.05​

[/TD]
[/TR]
</tbody>[/TABLE]

Slicers would be added for each piece of pivot data used.

I've answered a question on this forum this morning in the hope that my kindness will pay forward. Karma.

Thanks in advance!

 

[sheetspread]

Your first table structure is nonnormal mixed with normal data. Unless the normal source tables are available for writing queries, you have to unpivot using BI tools or this method for standalone Excel:

https://www.youtube.com/watch?v=xmqTN0X-AgY

Then you can pivot:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Row	InvWeek	TimeWeek	Column	Value	Value2
2	Bloggs, Joe	3/4/2018	1/7/2018	A	KG	105.69		InvWeek	Value	Row	TimeWeek	Sum of Value2
3	Bloggs, Joe	3/4/2018	1/7/2018	B				3/4/2018	KG	Bloggs, Joe	1/7/2018	105.69
4	Bloggs, Joe	3/4/2018	1/7/2018	C						Jones, Dave	1/7/2018	30.45
5	Wiggins, Bradley	3/4/2018	1/14/2018	A	KG	143.15				Wiggins, Bradley	1/14/2018	143.15
6	Wiggins, Bradley	3/4/2018	1/14/2018	B	RA	61.35			QSW	Jones, Dave	1/7/2018	71.05
7	Wiggins, Bradley	3/4/2018	1/14/2018	C					RA	Jones, Dave	1/7/2018	101.5
8	Knightly, Kiera	3/4/2018	1/21/2018	A	RA	199				Knightly, Kiera	1/21/2018	199
9	Knightly, Kiera	3/4/2018	1/21/2018	B						Wiggins, Bradley	1/14/2018	61.35
10	Knightly, Kiera	3/4/2018	1/21/2018	C
11	Jones, Dave	3/4/2018	1/7/2018	A	RA	101.5
12	Jones, Dave	3/4/2018	1/7/2018	B	KG	30.45
13	Jones, Dave	3/4/2018	1/7/2018	C	QSW	71.05
Sheet12

 

[andybrown115]

Excellent reply... the standalone Excel way worked brilliantly.

Interestingly, though, I've just started working with Power BI. I have my head around it in a simplistic fashion, but had't realised it would be an option for this stuff. Using that would be infinitely better than amending the core data.

To that end, I've spent the past two hours learning how to do it in Power BI, and the result is beautiful and slick

Thanks very much for giving both options. Both helped, and both work!

 

[sheetspread]