Concat (or similar effect) on Pivot Table Field and Sum with comma separation

[MattDanFrank2]

Hi all, thank you in advance for helping me solve this.

Here is an example of my data (simplified for illustration): excel 1.png

Here is what I'm trying to achieve: excel 2.png

It's the formula for column D I need help with, which needs to concat the CAR MODEL and COUNT OF CARS, if there is more than one CAR MODEL per ACCOUNT.

Thank you so much,

Matt

 

[RoryA]

Pictures don't really help us to help you - if you can use the XL2BB add-in, you can give us sample data to just copy and paste. It would also help if you update your profile to show what version of Office you are using, as that may affect what solutions are available to you. On that note, you could do this with Power Pivot, if you have it?

 

[MattDanFrank2]

Hi RoryA, thank you for responding to me. I have updated my profile (Office 365 with Power Pivot), and downloaded Xl2bb.

Book1
	A	B
1	ACCOUNT	Model
2	4	JEEP
3	6	VW
4	7	VW
5	9	FORD
6	12	AUDI
7	14	JEEP
8	16	FORD
9	20	VW
10	20	VW
11	20	FORD
12	55	FORD
13	77	AUDI
14	88	VW
15	88	VW
16	88	VW
17	99	AUDI
18	99	VW
Sheet1

Book1
	A	B	C	D
1	Count of Model
2	ACCOUNT	Model	Total
3	4	JEEP	1	JEEP x 1
4	6	VW	1	VW x 1
5	7	VW	1	VW x 1
6	9	FORD	1	FORD x 1
7	12	AUDI	1	AUDI x 1
8	14	JEEP	1	JEEP x 1
9	16	FORD	1	FORD x 1
10	20	FORD	1	FORD x 1, VW x 2
11		VW	2
12	55	FORD	1	FORD x 1
13	77	AUDI	1	AUDI x 1
14	88	VW	3	VW x 3
15	99	AUDI	1	AUDI x 1, VW x 1
16		VW	1
17	Grand Total		17
Sheet2

 

[RoryA]

OK, so make the data into a table (if it isn't already), load it into Power Pivot, then you'll need a new measure using a formula like this (I've assumed Table1 as the table name):

=VAR tempTable = 
        SUMMARIZE(
            'Table1',
            [Model],
            "Count",COUNTROWS('Table1')
        )
RETURN
    CONCATENATEX(tempTable,[Model] & " " & [Count] ,", ")

 

[MattDanFrank2]

Hi RoryA,

This is the result I'm getting. Is that what you expected, please?

 

[RoryA]

It needs to be a measure, not a calculated column. create a pivot table, right-click the table name in the Field List, and New measure