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):

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

leggings

<colgroup><col></colgroup><tbody>
</tbody>

Thanks in advance for any help. MJD

<colgroup><col></colgroup><tbody>
</tbody>

 

[cyrilbrd]

Hi,
just curious, what do you want to see into 'one cell'?
maybe you could post a very small sample of your raw data prior to the PT and what you wish to achieve as final result, such might allow members to give you better feedback.
To post small sample please read instructions in my signature below.

 

[Marcelo Branco]

I think that, in fact, your Pivot Table has 2 columns, i.e., two different fields

Try to change the Pivot Table layout to Tabular Form

Pivot Tables Tools > Design > in the Layout Group click Report Layout and pick Tabular Form

Probably you will see something like this (maybe with different field names)

	A​	B​
3​	Code​	Product​
4​	464654727​	dress​
5​	464654728​	hat​
6​		leggings​
7​		shirt​
8​		shoes​
9​		tie​
10​	464654729​	boots​
11​		dress​
12​		shirt​
13​	464654730​	shoes​
14​		tie​
15​	464654731​	leggings​
16​	464654732​	boots​
17​		dress​
18​		hat​
19​		shirt​
20​	464654733​	shoes​
21​	464654734​	tie​
22​	464654735​	boots​
23​		dress​
24​		hat​
25​		leggings​
26​		shirt​
27​		shoes​
28​		tie​
29​	464654736​	boots​
30​	464654737​	dress​
31​	464654738​	shirt​
32​	464654739​	shoes​
33​	464654740​	leggings​

Which cells do you want to concatenate?

M.

 

[MarkJamesDavies]

Hello, thank you for the reply.

I want to concatenate, for example, B5:B9 however, I want to be able to run the formula all the way down so the Code's product is in 1 cell.

So, in theory, if the A column has data in, the B columns data will be concatenated all the way until the next A cell has data in.

Example:

Excel 2012
	A	B	C	D
1		A	B
2	3	Code	Product	concatenated
3	4	464654727	dress	dress
4	5	464654728	hat	hat tie leggings shirt shoes
5	6		leggings
6	7		shirt
7	8		shoes
8	9		tie
9	10	464654729	boots	boots dress shirt
10	11		dress
11	12		shirt
12	13	464654730	shoes	shoes tie
13	14		tie
14	15	464654731	leggings	leggings
15	16	464654732	boots	boots dress hat shirt
16	17		dress
17	18		hat
18	19		shirt
19	20	464654733	shoes	shoes
20	21	464654734	tie	tie
21	22	464654735	boots	boots dress hat leggings shirt shoes tie
22	23		dress
23	24		hat
24	25		leggings
25	26		shirt
26	27		shoes
27	28		tie
28	29	464654736	boots	boots
29	30	464654737	dress	dress
30	31	464654738	shirt	shirt
31	32	464654739	shoes	shoes
32	33	464654740	leggings	leggings
Sheet1

Cell Formulas
Range	Formula
D4	=CONCATENATE(C4," ", C8, " ",C5, " ", C6, " ", C7)
D9	=CONCATENATE(C9," ",C10, " ",C11, " ")
D12	=CONCATENATE(C12, " ",C13)
D15	=CONCATENATE(C15, " ",C16, " ",C17, " ",C18, " ")
D21	=CONCATENATE(C21, " ",C22, " ",C23, " ",C24, " ",C25, " ", C26, " ",C27)

 

[MarkJamesDavies]

Please see my reply to the other user,

Thanks a lot of the HTML guide, amazing job my friend!! Really simple to use also.

Thanks again, hope you can help me.

MJD

 

[Joyner]

Hello, here is one option if you can use the ACONCAT, search other posts for details, but here is the code from one post (workbook will needs to be saved as xlsm)

ACONCAT is a work-alike written in VBA...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

The formula would then be this, data starting in A3:

=IF(A3<>A2,aconcat(B3:INDIRECT("B"&ROWS($A$1:A3)+COUNTIF($A$3:$A$32,A3)-1)," "),"")

 

[MarkJamesDavies]

Hello Joyner,

I just tried this formula and only #NAME? comes? Am I doing something wrong?

Apologies in advance if I am.

HTML:

Excel 2012
	A	B	C	D
1	Code	Product
2	464654727	dress		#NAME?
3	464654728	hat
4		leggings
5		shirt
6		shoes
7		tie
8	464654729	boots
9		dress
10		shirt
11	464654730	shoes
12		tie
13	464654731	leggings
14	464654732	boots
15		dress
16		hat
17		shirt
18	464654733	shoes
19	464654734	tie
20	464654735	boots
21		dress
22		hat
23		leggings
24		shirt
25		shoes
26		tie
27	464654736	boots
28	464654737	dress
29	464654738	shirt
30	464654739	shoes
31	464654740	legging
Sheet1

Cell Formulas
Range	Formula
D2	=IF(A3<>A2,aconcat(B3:INDIRECT("B"&ROWS($A$1:A3)+COUNTIF($A$3:$A$32,A3)-1)," "),"")

 

[Joyner]

Hello, did you put the aconcat code in a module? I am guessing that is the problem since you are getting the #NAME error, so Excel doesn't recognize a name in the formula. If not, the code needs to be copied into a vba/code module, so in for file click Alt F11, go to Insert; Module and copy the code in there and save.

AND...sorry I forgot to mention an important part, for my formula to work you need to "Repeat All Item Labels" (if you have Excel 2010 or later) - click in the Code column, and on the Design Tab, Layout group all the way to the left, Report Layout, and "Repeat All Item Labels". If you don't have at least 2010, we would need to fill blank on the data versus the pivot table, or rewrite the formula to use the blanks instead of the repeating code numbers.

The formula should work, but for the original formula the start the data was in A3 versus A2, For data starting in A2 use this instead:

=IF(A2<>A1,aconcat(B2:INDIRECT("B"&ROWS($A$1:A2)+COUNTIF($A$2:$A$31,A2)-1)," "),"")

 

[MarkJamesDavies]

Hello Joyner,

Thank you for your assistance again,

I've attempted the above however, I failed.

When I try to run the module, it states:

"Compile error:

Expected: line number or label or statement or end of statement"

I'm using Excel 2007 but I've also tried it on Excel 2013 also but no success here.

I've personally created macro's and modules to do other things in Excel but I just cannot get this to run correctly.

Thanks in advance for any help,

MJD.

 

[Joyner]

I can't tell you what that problem may be. I just tried to copy the code I gave you, to a new module and it works fine. The only thing I can think is that somehow you didn't copy and paste all of the code. I don't think there would be a reason why it would work on my computer and not on yours.

I would suggest you copy the code again to a module, then retry the formula.