Reporting in a Pivot Table with a field spread across multiple columns

[dylan_work]

I have a set of values presented to me in a repeated column list and want to (ideally) use Pivot Tables to report on them quickly.

When I include the repeated columns in my pivots, they are treated as individual values, and I can't seem to concatenate them easily.

I can see that I could transform this data with Power Query, but I want a simple Pivot Table solution if possible.

Book1
	A	B	C	D	E	F	G	H
1	ID	Area of concern	Area of concern	Area of concern	Area of concern			CHALLENGE:
2	a	area1	area2	area3	area4			Rows can be associated with 0..n Areas of Concern and we want to show
3	b	area2	area3					for each row the areas that are relevant in some sort of matrix.
4	c	area1	area2
5	d	area3
6	e	area1	area4
7
8	ACTUAL:
9	Count of Area of concern	Column Labels
10	Row Labels	area1	area2	area3	(blank)	Grand Total
11	a	1				1
12	area2	1				1
13	area3	1				1
14	area4	1				1
15	b		1			1
16	area3		1			1
17	(blank)		1			1
18	(blank)		1			1
19	c	1				1
20	area2	1				1
21	(blank)	1				1
22	(blank)	1				1
23	d			1		1
24	(blank)			1		1
25	(blank)			1		1
26	(blank)			1		1
27	e	1				1
28	area4	1				1
29	(blank)	1				1
30	(blank)	1				1
31	(blank)
32	(blank)
33	(blank)
34	(blank)
35	Grand Total	3	1	1		5
36
37
38	IDEAL:
39	Count of Area of concern	Column Labels
40	Row Labels	area1	area2	area3	area4	(blank)	Grand Total
41	a	1	1	1	1		4
42	b		1	1			2
43	c	1	1				2
44	d			1			1
45	e	1			1		2
46	Grand Total	3	3	3	2		11
47
Sheet1