Concatenate column names from a pivot table

[AusSteelMan]

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

	A	B	C
ACTUATOR: AIR REGISTERS BOILERS #2B/STN	BOILERS
ACTUATOR: PNEU SGL ACT STC GP085 550KPA	BOILERS
ACTUATOR: ROTORK 7A ELECT WIRING 1411.50	BOILERS
BEARING, ROL CYL: CRM14-C3 SKF 1.750IN	BOILERS
CARRIAGE ASSY: SOOTBLOWER IK520 25BOILER	BOILERS
ACTUATOR: PNEU SGL ACT S/R 90DEG ES200	BOILERS
ACTUATOR: SQ BOX KEYED 3/4" TAYLOR 3492	BOILERS
FLUID CPLG: 26"SCOOP CTRL FLUIDRV SCR24R	BOILERS
FLUID CPLG: 20"SCOOP CTRL FLUIDRV SCR24R	BOILERS
BELT, V: SPB3170 16 X 13MM SECTION	BOILERS
DELETED ITEM - NOT AVAILABLE	BOILERS
BARRICADE: BOILER O/HAULS #2B/STN	BOILERS
BEARING: NEEDLE ROLLER SJ7285+IR7285 RBC	BOILERS
NECK&LANTERN RING COMB: 21-23MAKEUP PUMP	BOILERS
ACTUATOR: AIR REGISTERS BOILERS #2B/STN	Trucks
ACTUATOR: PNEU SGL ACT STC GP085 550KPA	Cars
ACTUATOR: ROTORK 7A ELECT WIRING 1411.50	Trucks
BEARING, ROL CYL: CRM14-C3 SKF 1.750IN	Bananas
CARRIAGE ASSY: SOOTBLOWER IK520 25BOILER	Apples
BELLOWS: 100KPA 450NB FL AS2129D INCO625	Rockets
BELLOWS: 150KPA 200NB FLG AS2129D INC825	Rockets
BELLOWS: 150KPA 300NB FLG AS2129D INC825	Engines
BELLOWS: 150KPA 250NB FLG AS2129D INC825	Engines
BARRICADE: BOILER O/HAULS #2B/STN	PUMPS
BEARING: NEEDLE ROLLER SJ7285+IR7285 RBC	PUMPS
NECK&LANTERN RING COMB: 21-23MAKEUP PUMP	PUMPS
BARRICADE: BOILER O/HAULS #2B/STN	FANS
BEARING: NEEDLE ROLLER SJ7285+IR7285 RBC	FANS
NECK&LANTERN RING COMB: 21-23MAKEUP PUMP	FANS

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 79px"><COL style="WIDTH: 318px"><COL style="WIDTH: 73px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Object[/TD]
[TD="align: center"]Description[/TD]
[TD="align: center"]Class[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10041083[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]10042544[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]10042552[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10043203[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]10043901[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]10044217[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10044257[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]10044718[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10044730[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]10045728[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]10046893[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]10046896[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]10048618[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]10049388[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]10041083[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]10042544[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]10042552[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]10043203[/TD]

[TD="bgcolor: #cacaca, align: center"]20[/TD]
[TD="align: right"]10043901[/TD]

[TD="bgcolor: #cacaca, align: center"]21[/TD]
[TD="align: right"]10050190[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]
[TD="align: right"]10050193[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]
[TD="align: right"]10050196[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]
[TD="align: right"]10050199[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]
[TD="align: right"]10046896[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]
[TD="align: right"]10048618[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]
[TD="align: right"]10049388[/TD]

[TD="bgcolor: #cacaca, align: center"]28[/TD]
[TD="align: right"]10046896[/TD]

[TD="bgcolor: #cacaca, align: center"]29[/TD]
[TD="align: right"]10048618[/TD]

[TD="bgcolor: #cacaca, align: center"]30[/TD]
[TD="align: right"]10049388[/TD]

</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

	F	G	H	I	J	K
Countif	1st Class	2nd Class	3rd Class	StringConcat
BOILERS	Trucks		BOILERS, Trucks
BOILERS	Cars		BOILERS, Cars
BOILERS	Trucks		BOILERS, Trucks
BOILERS	Bananas		BOILERS, Bananas
BOILERS	Apples		BOILERS, Apples
BOILERS			BOILERS
BOILERS			BOILERS
BOILERS			BOILERS
BOILERS			BOILERS
BOILERS			BOILERS
BOILERS			BOILERS
BOILERS	PUMPS	FANS	BOILERS, PUMPS, FANS
BOILERS	PUMPS	FANS	BOILERS, PUMPS, FANS
BOILERS	PUMPS	FANS	BOILERS, PUMPS, FANS
Rockets			Rockets
Rockets			Rockets
Engines			Engines
Engines			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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Object[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]10041083[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]10042544[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]10042552[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]10043203[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]10043901[/TD]
[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]10044217[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]10044257[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]10044718[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]10044730[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]10045728[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]10046893[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]10046896[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]10048618[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]10049388[/TD]
[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]10050190[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]
[TD="align: right"]10050193[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]18[/TD]
[TD="align: right"]10050196[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]19[/TD]
[TD="align: right"]10050199[/TD]
[TD="align: right"]1[/TD]

</TBODY>

Spreadsheet Formulas

Cell Formula 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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Count of Description

Column Labels

Row Labels

2B/STNSV

30COGBLDR

ATC1

BLEEDR6BF

BOILERS

BOSVALVES

CIRCLIQ2

CIRCLIQ4

CIRCLIQ6

D085

D142

DEMIN

EXHAUSTER

FLUSHLIQ2

FLUSHLIQ3

FLUSHLIQ5

FPCH

OXYCLEAN

OXYLUB

OXYPACKIN

OXYVALVES

SPAREVALV

Grand Total

<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>
[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]10046178 - BAFFLE: EX/STM ENDOIL U2R 143&203M/45&63[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]10043112 - BEARING: STEAM END D-R U2R,143M&203M/38[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: left"]10112506 - STOP: CARBON RING D-R U2R 143M&203M /270[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: left"]10103159 - RING: CARBON 2.383"DIA D-R U2R&143M / 46[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: left"]10046185 - BAFFLE: GOVERNOR OIL D-R 143M&203M / 28[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: left"]10103167 - RING: CARBON 3.510"DIA D-R 203M / 46[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: left"]10046182 - BAFFLE: EXHAUST END OIL D-R 203M / 63[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: left"]10111927 - SPRING: CARBON RING D-R 203M / 403[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: left"]10111928 - SPRING: CARBON RING D-R U2R&143M / 403[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: left"]10103165 - RING: CARBON 3.507"DIA D-R 203M / 46[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: left"]10049975 - BEARING: TURBINE EXHAUST END D-R 203M/60[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

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

</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

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, EXHAUSTER, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5

CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, EXHAUSTER, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5

CIRCLIQ2, CIRCLIQ4, CIRCLIQ6, FLUSHLIQ2, FLUSHLIQ3, FLUSHLIQ5

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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ccccff"]Count of Description[/TD]
[TD="bgcolor: #ccccff"]Column Labels[/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]
[TD="bgcolor: #ccccff"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ccccff"]Row Labels[/TD]
[TD="bgcolor: #ccccff"]2B/STNSV[/TD]
[TD="bgcolor: #ccccff"]30COGBLDR[/TD]
[TD="bgcolor: #ccccff"]ATC1[/TD]
[TD="bgcolor: #ccccff"]BLEEDR6BF[/TD]
[TD="bgcolor: #ccccff"]BOILERS[/TD]
[TD="bgcolor: #ccccff"]BOSVALVES[/TD]
[TD="bgcolor: #ccccff"]CIRCLIQ2[/TD]
[TD="bgcolor: #ccccff"]CIRCLIQ4[/TD]
[TD="bgcolor: #ccccff"]CIRCLIQ6[/TD]
[TD="bgcolor: #ccccff"]D085[/TD]
[TD="bgcolor: #ccccff"]D142[/TD]
[TD="bgcolor: #ccccff"]DEMIN[/TD]
[TD="bgcolor: #ccccff"]EXHAUSTER[/TD]
[TD="bgcolor: #ccccff"]FLUSHLIQ2[/TD]
[TD="bgcolor: #ccccff"]FLUSHLIQ3[/TD]
[TD="bgcolor: #ccccff"]FLUSHLIQ5[/TD]
[TD="bgcolor: #ccccff"]FPCH[/TD]
[TD="bgcolor: #ccccff"]OXYCLEAN[/TD]
[TD="bgcolor: #ccccff"]OXYLUB[/TD]
[TD="bgcolor: #ccccff"]OXYPACKIN[/TD]
[TD="bgcolor: #ccccff"]OXYVALVES[/TD]
[TD="bgcolor: #ccccff"]SPAREVALV[/TD]
[TD="bgcolor: #ccccff"]Grand Total[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: left"]10046178 - BAFFLE: EX/STM ENDOIL U2R 143&203M/45&63[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: left"]10043112 - BEARING: STEAM END D-R U2R,143M&203M/38[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: left"]10112506 - STOP: CARBON RING D-R U2R 143M&203M /270[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: left"]10103159 - RING: CARBON 2.383"DIA D-R U2R&143M / 46[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

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

</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

 

[s hal]

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!

 

[AusSteelMan]

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

*	A	B
Data Set	*
Objects	Class
apple
apple
apple
banana
banana
PC
PC
truck
truck
car
car
bottle
bottle
bottle

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]123456[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]234567[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]345678[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]456789[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]741852[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]852963[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]963741[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: right"]123456[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: right"]234567[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: right"]345678[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: right"]456789[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: right"]123456[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: right"]234567[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]
[TD="align: right"]345678[/TD]

</TBODY>

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


This is the ideal looking output:

Sheet1

*	D	E
Results	*
Objects	Classes
apple, truck, bottle
apple, truck, bottle
apple, car, bottle
banana, car
banana
PC
PC

<COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 108px"></COLGROUP><TBODY>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]123456[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]234567[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]345678[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]456789[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]741852[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]852963[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]963741[/TD]

</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

*	H	I	J	K
Alternate result	*	*	*
Objects	Class 1	Class 2	Class 3
apple	truck	bottle
apple	truck	bottle
apple	car	bottle
banana	car	*
banana	*	*
PC	*	*
PC	*	*

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]123456[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]234567[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]345678[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]456789[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]741852[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]852963[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]963741[/TD]

</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

 

[s hal]

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!

 

[AusSteelMan]

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