Combining PivotTable Rows from Headers

[MarkJamesDavies]

Hello,

This is my first post, please be patient. I will put as much detail as possible.

I'm trying to export all of the data between headers from a pivot table into 1 cell but the items in between are inconsistent in terms of their cell creation. I tried the "=CONCATENATE" function however, due to the cells being inconsistent, it's very difficult to do.

Is it possible someone could help me with this problem? I visit this site a lot and has been wonderful.

Table Example (imagine this table in a 1 column pivot table with the numbers in a header):

[TABLE="width: 155"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"]464654727
[/TD]
[/TR]
[TR]
[TD="align: center"]dress[/TD]
[/TR]
[TR]
[TD="align: center"]464654728
[/TD]
[/TR]
[TR]
[TD="align: center"]hat[/TD]
[/TR]
[TR]
[TD="align: center"]leggings[/TD]
[/TR]
[TR]
[TD="align: center"]shirt[/TD]
[/TR]
[TR]
[TD="align: center"]shoes[/TD]
[/TR]
[TR]
[TD="align: center"]tie[/TD]
[/TR]
[TR]
[TD="align: center"]464654729[/TD]
[/TR]
[TR]
[TD="align: center"]boots[/TD]
[/TR]
[TR]
[TD="align: center"]dress[/TD]
[/TR]
[TR]
[TD="align: center"]shirt[/TD]
[/TR]
[TR]
[TD="align: center"]464654730[/TD]
[/TR]
[TR]
[TD="align: center"]shoes[/TD]
[/TR]
[TR]
[TD="align: center"]tie[/TD]
[/TR]
[TR]
[TD="align: center"]464654731[/TD]
[/TR]
[TR]
[TD="align: center"]leggings[/TD]
[/TR]
[TR]
[TD="align: center"]464654732[/TD]
[/TR]
[TR]
[TD="align: center"]boots[/TD]
[/TR]
[TR]
[TD="align: center"]dress[/TD]
[/TR]
[TR]
[TD="align: center"]hat[/TD]
[/TR]
[TR]
[TD="align: center"]shirt[/TD]
[/TR]
[TR]
[TD="align: center"]464654733[/TD]
[/TR]
[TR]
[TD="align: center"]shoes[/TD]
[/TR]
[TR]
[TD="align: center"]464654734[/TD]
[/TR]
[TR]
[TD="align: center"]tie[/TD]
[/TR]
[TR]
[TD="align: center"]464654735[/TD]
[/TR]
[TR]
[TD="align: center"]boots[/TD]
[/TR]
[TR]
[TD="align: center"]dress[/TD]
[/TR]
[TR]
[TD="align: center"]hat[/TD]
[/TR]
[TR]
[TD="align: center"]leggings[/TD]
[/TR]
[TR]
[TD="align: center"]shirt[/TD]
[/TR]
[TR]
[TD="align: center"]shoes[/TD]
[/TR]
[TR]
[TD="align: center"]tie[/TD]
[/TR]
[TR]
[TD="align: center"]464654736[/TD]
[/TR]
[TR]
[TD="align: center"]boots[/TD]
[/TR]
[TR]
[TD="align: center"]464654737[/TD]
[/TR]
[TR]
[TD="align: center"]dress[/TD]
[/TR]
[TR]
[TD="align: center"]464654738[/TD]
[/TR]
[TR]
[TD="align: center"]shirt[/TD]
[/TR]
[TR]
[TD="align: center"]464654739[/TD]
[/TR]
[TR]
[TD="align: center"]shoes[/TD]
[/TR]
[TR]
[TD="align: center"]464654740[/TD]
[/TR]
[TR]
[TD="align: center"]leggings[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 155"]
<colgroup><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks in advance for any help.

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

 

[cyrilbrd]

Maybe like this?
Given in A1
Report Layout formatted as "how in Outline form"

Excel 2013
	A	B	C	D	E	F	G
1	Code	Product
2	464654727	dress
3	464654728	hat		Code	Product
4	464654728	leggings		464654727			dress
5	464654728	shirt		464654727	dress
6	464654728	shoes		464654728			hat, leggings, shirt, shoes, tie
7	464654728	tie		464654728	hat
8	464654729	boots		464654728	leggings
9	464654729	dress		464654728	shirt
10	464654729	shirt		464654728	shoes
11	464654730	shoes		464654728	tie
12	464654730	tie		464654729			boots, dress, shirt
13	464654731	leggings		464654729	boots
14	464654732	boots		464654729	dress
15	464654732	dress		464654729	shirt
16	464654732	hat		464654730			shoes, tie
17	464654732	shirt		464654730	shoes
18	464654733	shoes		464654730	tie
19	464654734	tie		464654731			leggings
20	464654735	boots		464654731	leggings
21	464654735	dress		464654732			boots, dress, hat, shirt
22	464654735	hat		464654732	boots
23	464654735	leggings		464654732	dress
24	464654735	shirt		464654732	hat
25	464654735	shoes		464654732	shirt
26	464654735	tie		464654733			shoes
27	464654736	boots		464654733	shoes
28	464654737	dress		464654734			tie
29	464654738	shirt		464654734	tie
30	464654739	shoes		464654735			boots, dress, hat, leggings, shirt, shoes, tie
31	464654740	leggings		464654735	boots
32				464654735	dress
33				464654735	hat
34				464654735	leggings
35				464654735	shirt
36				464654735	shoes
37				464654735	tie
38				464654736			boots
39				464654736	boots
40				464654737			dress
41				464654737	dress
42				464654738			shirt
43				464654738	shirt
44				464654739			shoes
45				464654739	shoes
46				464654740			leggings
47				464654740	leggings
MarkJamesDavies

code is

Option Explicit' created by allquixotic 130305
Function Pivot_conc(param As Variant, search As Range, values As Range, Optional absolute As Boolean = False) As String


Dim sep As String, retval As String
Dim i As Integer, rownum As Integer
Dim look As Range, j As Range


sep = ", "
retval = ""
For i = 1 To search.Rows.count
Set look = search.Cells(i, 1)
If absolute Then
        rownum = look.Row
Else
        rownum = i
End If


If look.Value = param Then
        If absolute Then
                Set j = values.Worksheet.Cells(rownum, values.Column)
        Else
                Set j = values.Cells(i, 1)
        End If
        retval = IIf(retval = "", retval & j.Value, retval & sep & j.Value)
End If


Next

Pivot_conc = retval


End Function

Formula in G4 is =IF(ISBLANK(E4),Pivot_conc(D4,$D$4:$D$47,$E$4:$E$47,TRUE),"") copied down till needed.

 

[MarkJamesDavies]

Hi Joyner,

I've tried this but for some reason, I just cannot get this to work. I need to reply to the other person who commented. I also need to revise the need - on my original post, I didn't have a clue how to HTML make the spreadsheet so I can re-do this thread and I believe it may be easier.

Thanks for your help so far, I wish I could say thanks in person.

MJD

 

[MarkJamesDavies]

Hi,thanks for your reply and help so far.

I was unable to do the above - I just cannot get anything to work for some reason.

I keep trying to run modules but nothing really works for some reason.

I will go about this another way using the HTML maker tool.

The below is a pivoted table of data that needs somewhat splitting.

Excel 2010
	A
1	Row Labels
2	464654727
3	dress
4	464654728
5	hat
6	leggings
7	shirt
8	shoes
9	tie
10	464654729
11	boots
12	dress
13	shirt
14	464654730
15	shoes
16	tie
17	464654731
18	leggings
19	464654732
20	boots
21	dress
22	hat
23	shirt
24	464654733
25	shoes
26	464654734
27	tie
28	464654735
29	boots
30	dress
31	hat
32	leggings
33	shirt
34	shoes
35	tie
36	464654736
37	boots
38	464654737
39	dress
40	464654738
41	shirt
42	464654739
43	shoes
44	464654740
45	legging
46	(blank)
47	(blank)
48	Grand Total
Sheet5

Cell Formulas
Range	Formula
A1	Row Labels
A2	464654727
A3	dress
A4	464654728
A5	hat
A6	leggings
A7	shirt
A8	shoes
A9	tie
A10	464654729
A11	boots
A12	dress
A13	shirt
A14	464654730
A15	shoes
A16	tie
A17	464654731
A18	leggings
A19	464654732
A20	boots
A21	dress
A22	hat
A23	shirt
A24	464654733
A25	shoes
A26	464654734
A27	tie
A28	464654735
A29	boots
A30	dress
A31	hat
A32	leggings
A33	shirt
A34	shoes
A35	tie
A36	464654736
A37	boots
A38	464654737
A39	dress
A40	464654738
A41	shirt
A42	464654739
A43	shoes
A44	464654740
A45	legging
A46	(blank)
A47	(blank)
A48	Grand Total

All I need to do is to literally concanetrate the cells between the codes in the headers - I've tried doing this for weeks and you guys are my only hope. I'm quite new to modules but I've created my own Macro's so something might need explaining if possible.

What I need:

<a href="http://s71.photobucket.com/user/MarkDavies16/media/egeagaeb.png.html" target="_blank"><img src="http://i71.photobucket.com/albums/i125/MarkDavies16/egeagaeb.png" border="0" alt=" photo egeagaeb.png"/></a>

I hope this isn't too much to ask and I thank you both and all for your patience.

Thanks again

MJD.

 

[MarkJamesDavies]

Hi,thanks for your reply and help so far.

I was unable to do the above - I just cannot get anything to work for some reason.

I keep trying to run modules but nothing really works for some reason.

I will go about this another way using the HTML maker tool.

The below is a pivoted table of data that needs somewhat splitting.

Excel 2010

	A
Row Labels
464654727
dress
464654728
hat
leggings
shirt
shoes
tie
464654729
boots
dress
shirt
464654730
shoes
tie
464654731
leggings
464654732
boots
dress
hat
shirt
464654733
shoes
464654734
tie
464654735
boots
dress
hat
leggings
shirt
shoes
tie
464654736
boots
464654737
dress
464654738
shirt
464654739
shoes
464654740
legging
(blank)
(blank)
Grand Total

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

[TD="align: center"]35[/TD]

[TD="align: center"]36[/TD]

[TD="align: center"]37[/TD]

[TD="align: center"]38[/TD]

[TD="align: center"]39[/TD]

[TD="align: center"]40[/TD]

[TD="align: center"]41[/TD]

[TD="align: center"]42[/TD]

[TD="align: center"]43[/TD]

[TD="align: center"]44[/TD]

[TD="align: center"]45[/TD]

[TD="align: center"]46[/TD]

[TD="align: center"]47[/TD]

[TD="align: center"]48[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]Row Labels[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]464654727[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A3[/TH]
[TD="align: left"]dress[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]464654728[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]hat[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A6[/TH]
[TD="align: left"]leggings[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A7[/TH]
[TD="align: left"]shirt[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A8[/TH]
[TD="align: left"]shoes[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A9[/TH]
[TD="align: left"]tie[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A10[/TH]
[TD="align: left"]464654729[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A11[/TH]
[TD="align: left"]boots[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A12[/TH]
[TD="align: left"]dress[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A13[/TH]
[TD="align: left"]shirt[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A14[/TH]
[TD="align: left"]464654730[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A15[/TH]
[TD="align: left"]shoes[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A16[/TH]
[TD="align: left"]tie[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A17[/TH]
[TD="align: left"]464654731[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A18[/TH]
[TD="align: left"]leggings[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A19[/TH]
[TD="align: left"]464654732[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A20[/TH]
[TD="align: left"]boots[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A21[/TH]
[TD="align: left"]dress[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A22[/TH]
[TD="align: left"]hat[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A23[/TH]
[TD="align: left"]shirt[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A24[/TH]
[TD="align: left"]464654733[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A25[/TH]
[TD="align: left"]shoes[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A26[/TH]
[TD="align: left"]464654734[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A27[/TH]
[TD="align: left"]tie[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A28[/TH]
[TD="align: left"]464654735[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A29[/TH]
[TD="align: left"]boots[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A30[/TH]
[TD="align: left"]dress[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A31[/TH]
[TD="align: left"]hat[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A32[/TH]
[TD="align: left"]leggings[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A33[/TH]
[TD="align: left"]shirt[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A34[/TH]
[TD="align: left"]shoes[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A35[/TH]
[TD="align: left"]tie[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A36[/TH]
[TD="align: left"]464654736[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A37[/TH]
[TD="align: left"]boots[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A38[/TH]
[TD="align: left"]464654737[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A39[/TH]
[TD="align: left"]dress[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A40[/TH]
[TD="align: left"]464654738[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A41[/TH]
[TD="align: left"]shirt[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A42[/TH]
[TD="align: left"]464654739[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A43[/TH]
[TD="align: left"]shoes[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A44[/TH]
[TD="align: left"]464654740[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A45[/TH]
[TD="align: left"]legging[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A46[/TH]
[TD="align: left"](blank)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A47[/TH]
[TD="align: left"](blank)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A48[/TH]
[TD="align: left"]Grand Total[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



All I need to do is to literally concanetrate the cells between the codes in the headers - I've tried doing this for weeks and you guys are my only hope. I'm quite new to modules but I've created my own Macro's so something might need explaining if possible.

What I need:



I hope this isn't too much to ask and I thank you both and all for your patience.

Thanks again

MJD.

Here is the actual PivotTable;

<a href="http://s71.photobucket.com/user/MarkDavies16/media/pivot.png.html" target="_blank"><img src="http://i71.photobucket.com/albums/i125/MarkDavies16/pivot.png" border="0" alt=" photo pivot.png"/></a>

Thanks again.

MJD.

 

[cyrilbrd]

Here is the actual PivotTable;



Thanks again.

MJD.

Set your pivot table as follows Report Layout "Show in Outline Form" then "Repeat all Item Labels"
Next open developer / visual basic / Insert / Module
Paste code.

Save as .xlsm
Run macro within formula.

 

[Aleksotas]

Have done all like written above in post 11 and 15. Have copied and pasted all code and saves project as .xlsm file. But have no clue how to "Run macro within formula". Simply when i copy formula [TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]=IF(ISBLANK(E4),Pivot_conc(D4,$D$4:$D$47,$E$4:$E$47,TRUE),"" nothing happens. I guess i do not do some easy step. Please advise if possible.
ALeksandr [/TD]
[/TR]
</tbody>[/TABLE]

 

[Joyner]

If you save as .xlsm, you will then need to "Enable Content". If you don't see the button above the Formula Bar, then just close the file and reopen it. Once you have enabled the content, I don't think you have to do anything with the macro, but I haven't used that one provided. I provided and tested the ACONCAT and formula shown in one of the previous posts here. The function will allow you to use "ACONCAT" like any other formula. Also, it looks like you have shown the exact formula posted in #11, you would need to change it to suit your Pivot Table.

I'll read through the other posts and try that solution and Function "Picot_conc" and see how that one works.

 

[Joyner]

Hello ALeksandr,

I tried both Functions and they both work fine; I had to adjust the formulas for your range. For my Function, your data was laid out slightly different than my Function was written for, so I had to adjust my formula. I also added a comma, "," as cyrilbrds's does since mine originally didn't have one because the original poster did not request that, but it certainly makes sense. So here are the two formulas that work with your data range:

Joyner:

=SUBSTITUTE(IF(A2<>A1,aconcat(B2:INDIRECT("B"&ROWS($A$1:A2)+COUNTIF($A$3:$A$692,A2)),", "),""),", ","",1)

cyrilbrd:

=IF(ISBLANK(E4),Pivot_conc(D4,$D$4:$D$694,$E$4:$E$694,TRUE),"")

Note that the cyrilbrd Function is much cleaner and that mine uses a volatile function (Indirect), so you will want to use the cyrilbrd version (I think I will also adapt it for other uses).

I have emailed you the completed files. Let me know if you have any questions.

 

[cyrilbrd]

Aleksotas, you only forgot a parenthesis )
kindly change formula from=IF(ISBLANK(E4),Pivot_conc(D4,$D$4:$D$47,$E$4:$E$47,TRUE),"" to=IF(ISBLANK(E4),Pivot_conc(D4,$D$4:$D$47,$E$4:$E$47,TRUE),"")
Hope this clarifies things a bit.