Concatenate column names from a pivot table

A

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
Hi everyone,

I am hoping someone can help me with a pivot table problem I am having.

I have a large data set and have used a pivot table to summarise the COUNT of each row (Objects) for each Column Name (that is, I did what Pivot Tables do).

What I'd like to do in a new column is show a string of all the column names per row name.


That doesn't sound clear at all.
I'll try explaining that again from the start.


I have a data set that looks like this:

Sheet3

ABC
1ObjectDescriptionClass
210041083ACTUATOR: AIR REGISTERS BOILERS #2B/STNBOILERS
310042544ACTUATOR: PNEU SGL ACT STC GP085 550KPABOILERS
410042552ACTUATOR: ROTORK 7A ELECT WIRING 1411.50BOILERS
510043203BEARING, ROL CYL: CRM14-C3 SKF 1.750INBOILERS
610043901CARRIAGE ASSY: SOOTBLOWER IK520 25BOILERBOILERS
710044217ACTUATOR: PNEU SGL ACT S/R 90DEG ES200BOILERS
810044257ACTUATOR: SQ BOX KEYED 3/4" TAYLOR 3492BOILERS
910044718FLUID CPLG: 26"SCOOP CTRL FLUIDRV SCR24RBOILERS
1010044730FLUID CPLG: 20"SCOOP CTRL FLUIDRV SCR24RBOILERS
1110045728BELT, V: SPB3170 16 X 13MM SECTIONBOILERS
1210046893DELETED ITEM - NOT AVAILABLEBOILERS
1310046896BARRICADE: BOILER O/HAULS #2B/STNBOILERS
1410048618BEARING: NEEDLE ROLLER SJ7285+IR7285 RBCBOILERS
1510049388NECK&LANTERN RING COMB: 21-23MAKEUP PUMPBOILERS
1610041083ACTUATOR: AIR REGISTERS BOILERS #2B/STNTrucks
1710042544ACTUATOR: PNEU SGL ACT STC GP085 550KPACars
1810042552ACTUATOR: ROTORK 7A ELECT WIRING 1411.50Trucks
1910043203BEARING, ROL CYL: CRM14-C3 SKF 1.750INBananas
2010043901CARRIAGE ASSY: SOOTBLOWER IK520 25BOILERApples
2110050190BELLOWS: 100KPA 450NB FL AS2129D INCO625Rockets
2210050193BELLOWS: 150KPA 200NB FLG AS2129D INC825Rockets
2310050196BELLOWS: 150KPA 300NB FLG AS2129D INC825Engines
2410050199BELLOWS: 150KPA 250NB FLG AS2129D INC825Engines
2510046896BARRICADE: BOILER O/HAULS #2B/STNPUMPS
2610048618BEARING: NEEDLE ROLLER SJ7285+IR7285 RBCPUMPS
2710049388NECK&LANTERN RING COMB: 21-23MAKEUP PUMPPUMPS
2810046896BARRICADE: BOILER O/HAULS #2B/STNFANS
2910048618BEARING: NEEDLE ROLLER SJ7285+IR7285 RBCFANS
3010049388NECK&LANTERN RING COMB: 21-23MAKEUP PUMPFANS

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 318px"><COL style="WIDTH: 73px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

So I can pivot table to help with other analysis, but what I'd really like to get is a list of unique "Objects" (easy) with all the related "Class"es listed in column (or straight to concatenated). I can concatenate using Chip Pearson's StringConcat.

Here is an example:
Sheet3

FGHIJK
1ObjectCountif1st Class2nd Class3rd ClassStringConcat
2100410832BOILERSTrucks BOILERS, Trucks
3100425442BOILERSCars BOILERS, Cars
4100425522BOILERSTrucks BOILERS, Trucks
5100432032BOILERSBananas BOILERS, Bananas
6100439012BOILERSApples BOILERS, Apples
7100442171BOILERS BOILERS
8100442571BOILERS BOILERS
9100447181BOILERS BOILERS
10100447301BOILERS BOILERS
11100457281BOILERS BOILERS
12100468931BOILERS BOILERS
13100468963BOILERSPUMPSFANSBOILERS, PUMPS, FANS
14100486183BOILERSPUMPSFANSBOILERS, PUMPS, FANS
15100493883BOILERSPUMPSFANSBOILERS, PUMPS, FANS
16100501901Rockets Rockets
17100501931Rockets Rockets
18100501961Engines Engines
19100501991Engines Engines

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 67px"><COL style="WIDTH: 77px"><COL style="WIDTH: 82px"><COL style="WIDTH: 80px"><COL style="WIDTH: 151px"></COLGROUP><TBODY>
</TBODY>

Spreadsheet Formulas
CellFormula
G2=COUNTIF(A:A,F2)
K2=stringconcat(", ",TRUE,Table7[[#This Row],[1st Class]:[3rd Class]])

<TBODY>
</TBODY>

<TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


You can see Chip's Function in col K.

What I'd like to happen is in Cols H to J be automatically populated.
(For this example, I have typed in what I'd like the result to look like)

I considered using Vlookups (with helper column for 2nd & successive instances), INDEX/MATCH with SMALL to get 2nd+ instances and even lengthy IF statements. The main problem with IF is that in the real data set, I can have over 30 Classes.
I looked at Pivot table to see what I could do but haven't much experience in manipulating past regular feature.
I also took a cut&paste of the pivot table (as shown in part below) to see what I could do.

Here is a clip straight from the Pivot Table:
Pivot

ABCDEFGHIJKLMNOPQRSTUVWX
3Count of DescriptionColumn Labels
4Row Labels2B/STNSV30COGBLDRATC1BLEEDR6BFBOILERSBOSVALVESCIRCLIQ2CIRCLIQ4CIRCLIQ6D085D142DEMINEXHAUSTERFLUSHLIQ2FLUSHLIQ3FLUSHLIQ5FPCHOXYCLEANOXYLUBOXYPACKINOXYVALVESSPAREVALVGrand Total
510046178 - BAFFLE: EX/STM ENDOIL U2R 143&203M/45&63 111 1111 7
610043112 - BEARING: STEAM END D-R U2R,143M&203M/38 111 1111 7
710112506 - STOP: CARBON RING D-R U2R 143M&203M /270 111 111 6
810103159 - RING: CARBON 2.383"DIA D-R U2R&143M / 46 1 111 4
910046185 - BAFFLE: GOVERNOR OIL D-R 143M&203M / 28 11 11 4
1010103167 - RING: CARBON 3.510"DIA D-R 203M / 46 1 1 1 3
1110046182 - BAFFLE: EXHAUST END OIL D-R 203M / 63 1 1 1 3
1210111927 - SPRING: CARBON RING D-R 203M / 403 11 1 3
1310111928 - SPRING: CARBON RING D-R U2R&143M / 403 1 11 3
1410103165 - RING: CARBON 3.507"DIA D-R 203M / 46 1 1 1 3
1510049975 - BEARING: TURBINE EXHAUST END D-R 203M/60 1 1 1 3

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 412px"><COL style="WIDTH: 116px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 79px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

And one from the cut&paste of the pivot table, which also shows the desired outcome in the right hand column:


Sheet2

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1Count of DescriptionColumn Labels
2Row Labels2B/STNSV30COGBLDRATC1BLEEDR6BFBOILERSBOSVALVESCIRCLIQ2CIRCLIQ4CIRCLIQ6D085D142DEMINEXHAUSTERFLUSHLIQ2FLUSHLIQ3FLUSHLIQ5FPCHOXYCLEANOXYLUBOXYPACKINOXYVALVESSPAREVALVGrand Total
310046178 - BAFFLE: EX/STM ENDOIL U2R 143&203M/45&63 111 1111 7 CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, EXHAUSTER, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5
410043112 - BEARING: STEAM END D-R U2R,143M&203M/38 111 1111 7 CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, EXHAUSTER, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5
510112506 - STOP: CARBON RING D-R U2R 143M&203M /270 111 111 6 CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5
610103159 - RING: CARBON 2.383"DIA D-R U2R&143M / 46 1 111 4 CIRCLIQ2, EXHAUSTER, FLUSHLIQ2, FLUSHLIQ3

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 382px"><COL style="WIDTH: 100px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 28px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 508px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4



So after trying a few things, I thought I would consult the mass-brain that is MrExcel.

Can anybody please help. I would also be fine using <ACRONYM title="visual basic for applications">VBA</ACRONYM>, but its been a while since I did any basic coding so I wouldn't really stand a chance of starting from scratch.

Thanks very much for even just reading this question.
Regards,
Darren
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
I'm not following what you're trying to do, and you have too much for examples (I know that sounds strange, but I have to scroll around and can't see much of what you're talking about). Is there a reason you can't just drag the "objects" field into your pivot table to get these numbers? I'm not following the reason it needs to be outside of a pivot table!
 
Upvote 0
Hi again,

Sorry about that - It was late when I posted and I had to leave.
The pivot table was an aside really. I was using it for something else (so it already existed). So I tried getting the result I wanted using that.

So I'll start again...

I have a data set that has:
* object numbers in Col A
* "Class" codes in Col B

For many of the objects, there is more than 1 Class code.

My ultimate aim is to have a list of objects that is unique with the list of all relevant classes in the next column.
However, I can compromise and have each Class in a separate column if it makes the solution easier.

For Example:
Data set

Sheet1

*AB
1Data Set*
2ObjectsClass
3123456apple
4234567apple
5345678apple
6456789banana
7741852banana
8852963PC
9963741PC
10123456truck
11234567truck
12345678car
13456789car
14123456bottle
15234567bottle
16345678bottle

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


This is the ideal looking output:

Sheet1

*DE
1Results*
2ObjectsClasses
3123456apple, truck, bottle
4234567apple, truck, bottle
5345678apple, car, bottle
6456789banana, car
7741852banana
8852963PC
9963741PC

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 108px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4



But I am also fine with this as a result:

Sheet1

*HIJK
1Alternate result***
2ObjectsClass 1Class 2Class 3
3123456appletruckbottle
4234567appletruckbottle
5345678applecarbottle
6456789bananacar*
7741852banana**
8852963PC**
9963741PC**

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
</TBODY>


Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4


Hopefully this makes much more sense.

Again sorry for the confusion I created with the story telling of what I had tried. I should have just focussed on the problem.

Thanks very much.
Darren
 
Upvote 0
So you could do this in a formula, however, I'm hesitant to say it works unless your data set is relatively small, but in any case, this is the formula:

=INDEX(class_array,SMALL(IF(FREQUENCY(IF(class_array<>"",IF(cell_with_object=object_array,MATCH(class_array,class_array,0))),ROW(INDIRECT("1:100"))),ROW(INDIRECT("1:100"))),COLUMNS($A$1:A1)))

ENTER WITH CTRL-SHIFT-ENTER

The above has a couple problems other than taking FOREVER to calculate once your data reaches a certain size: (1) only counts the first 100 unique values - if you have more than that, you would need to change the 100 to a larger number (if you have more than 5, I'd say this probably isn't going to do much for you anyway). (2) puts the values in one cell at a time - like your last sheet.

Hope this helps!
 
Upvote 0
Thanks very much s hal.

It doesn't surprise me that an array formula was the answer. That said, I could not have got to your solution. Thankyou so much.

I had over 1300 unique items and up to 7 classes. So I changed calculation to Manual, set the formula in place, copied to all the cells required, SAVEd, braced myself, and hit F9.

All in all, it wasn't too bad. It only took about 5 minutes - maybe a little longer - to complete the recalc.

I then SAVEd again (which forced a recalc, so another 5 mins), then Copy-PasteValues and SAVEd a new copy.

I have more to run, but now I can thanks to your help.

Best regards,
Darren
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Countif with Index on a Pivot Table
Replies
1
Views
815
RasGhul
RasGhul
H
  • Question
comparing values in a column against multiple sheets and returning subsequent values from other columns
Replies
1
Views
873
DanteAmor
DanteAmor
How_Do_I
  • Question
Index Match
Replies
2
Views
777
How_Do_I
How_Do_I
A
  • Question
Pivot Table Challenge - Arrays?
Replies
1
Views
514
actjfc
A
H
  • Question
Compute Average in Pivot Table
Replies
0
Views
425
howard
H

Forum statistics

Threads
1,214,615
Messages
6,120,538
Members
448,970
Latest member
kennimack

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top