Concatenate option in pivot

@ sryadav12

I do not know of a way to do this directly in Pivot tables. If it is acceptable to do the concatenation in the source table this seems to do what is described above.

In Sheet1 of the attached find a column (D) labeled Concat. In that column find this formula

Formula:

Please Login or Register  to view this content.

This requires a User Defined Function. This one was written by 'tigeravatar'. The code is below to be copied and pasted into a module in the VBA editor. The "Personal.xlsb!" prefix will need to be removed. That is there because that is where I store UDFs. This means that the file will present errors upon download. When adjustments are made the table in Sheet1 will look like this:

Row\Col	A	B	C	D
1	SO	PRO	Ref	Concat
2	SO080310	PRO-126146	100000529	PRO-126146, PRO-126147
3		PRO-126147	100000529
4	SO080311	PRO-126148	100000530	PRO-126148
5	SO080312	PRO-126149	100000531	PRO-126149
6	SO080313	PRO-126150	100000532	PRO-126150
7	SO080314	PRO-126151	100000533	PRO-126151
8	SO080315	PRO-126152	100000534	PRO-126152
9	SO080316	PRO-126153	100000535	PRO-126153
10	SO080317	PRO-126154	100000536	PRO-126154
11	SO080318	PRO-126155	100000537	PRO-126155
12	SO080319	PRO-126156	100000538	PRO-126156, PRO-126157
13		PRO-126157	100000538
14	SO080320	PRO-126158	100000539	PRO-126158, PRO-126159, PRO-126160, PRO-126161, PRO-126162
15		PRO-126159	100000539
16		PRO-126160	100000539
17		PRO-126161	100000539
18		PRO-126162	100000539
19	SO080321	PRO-126163	100000540	PRO-126163
20	SO080322	PRO-126164	100000541	PRO-126164
21	SO080323	PRO-126165	300000316	PRO-126165
22	SO080324	PRO-126166	300000318	PRO-126166

The UDF code is here:

Please Login or Register  to view this content.

Then change the data source for the Pivot table and update.

The Pivot table looks like this:

Row\Col	A	B	C	D
3	Ref	PRO	SO	Concat
4	100000529	PRO-126146	SO080310	PRO-126146, PRO-126147
5		PRO-126147	(blank)
6	100000530	PRO-126148	SO080311	PRO-126148
7	100000531	PRO-126149	SO080312	PRO-126149
8	100000532	PRO-126150	SO080313	PRO-126150
9	100000533	PRO-126151	SO080314	PRO-126151
10	100000534	PRO-126152	SO080315	PRO-126152
11	100000535	PRO-126153	SO080316	PRO-126153
12	100000536	PRO-126154	SO080317	PRO-126154
13	100000537	PRO-126155	SO080318	PRO-126155
14	100000538	PRO-126156	SO080319	PRO-126156, PRO-126157
15		PRO-126157	(blank)
16	100000539	PRO-126158	SO080320	PRO-126158, PRO-126159, PRO-126160, PRO-126161, PRO-126162
17		PRO-126159	(blank)
18		PRO-126160	(blank)
19		PRO-126161	(blank)
20		PRO-126162	(blank)
21	100000540	PRO-126163	SO080321	PRO-126163
22	100000541	PRO-126164	SO080322	PRO-126164
23	300000316	PRO-126165	SO080323	PRO-126165
24	300000318	PRO-126166	SO080324	PRO-126166
25	Grand Total
26

Does this do what you want.