Crosstab Query Excel String Data

[CassidyHunt]

I have a set of data that I want to basically build a pivot table on but instead of performing some calculations on the data I would like to concatenate all the values with a comma or something. Basically have it look like this

        Lev1      Lev2      Lev3
Item1   red,blue            green, black
Item2   cyan      red,green
Item3                       Blue

My data is structured as such

Item1   Lev1   Red
Item1   Lev1   Blue
Item1   Lev3   Green
Item1   Lev3   Black
Item2   Lev1   Cyan
Item2   Lev2   Red
Item2   Lev2   Green
Item3   Lev3   Blue

Hopefully this makes sense.

Thanks

Cassidy

 

[GlennUK]

Can you use add-ins? Laurent Longre's free MOREFUNC add-in has a function named MCONCAT, which would help you. See here:

http://xcell05.free.fr/english/

An array formula ( entered using Ctrl-Shift-Enter instead of Enter ) like this:

=SUBSTITUTE(TRIM(MCONCAT(IF($A$1:$A$8=$A11,IF($B$1:$B$8=B$10,$C$1:$C$8&" ",""),"")))," ",", ")

should do it. ( assuming your souce table is in A1:C8, and your output table has the labels in A11:A13 and B1010 )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

.. or normalize your table using excellent trick by Skip Vought (faq68-5287).

combo

 

[CassidyHunt]

If you look at the source table data it is already normalized. I am wanting to transform it to the pivot table format above the second example.

 

[CassidyHunt]

The problem I found with the function is it doesn't appear in the pivot tables list of available functions. So when I use a pivot table to transform my normalized data all I get access to are sum, count, avg, etc... Maybe there is another way to handle this. The reason I was looking at a pivot table is because I have this tied to my Oracle database and it will dynamically increase in rows and columns. Everytime the pivot table refreshes then it would increase dynamically instead of me having specify the structure.

That function worked on my normalized data but I still couldn't make a the value display in the pivot table.

Thanks

Cassidy

 

[GlennUK]

So, are you saying that my method would be OK if you had a way of having the labels for the output table being updated automatically?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[xlbo]

Pivot tables cannot handle text as the VALUE item

You cannot, therefore use one for this purpose

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[CassidyHunt]

GlennUK,

Yes that is exactly what I am saying.

xlbo,

I understand that is the not the normal purpose of the pivot table. I am thinking that just like most Microsoft products there is a way to expand the functionality of the pivot table either through VBA or C# in an office product to provide a custom formula that would add this functionality. Better yet I am sure I am not the first one to want this and hoped someone out here might know of a product that I could buy or download that has that modification already in it.

Thanks

Cassidy.

 

[xlbo]

nope

pivot tables are a way of doing what is known as contextual aggregation. You cannot aggregate a string so you cannot use a pivot table for this. In BI parlane, strings are dimensions and cannot be aggregated. Only measures can be aggregated

There is cetainly no way to manipulate a pivot table to do this in VBA. Maybe C++ but I would doubt C#...



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Get a list of unique items by using an array formula like this:

=IF(ISERROR(MATCH(0,COUNTIF(F$2:F2,$A$1:$A$1999&""),0)),"",
INDEX(IF(ISBLANK($A$1:$A$1999),"",$A$1:$A$1999),MATCH(0,COUNTIF(F$2:F2,$A$1:$A$1999&""),0)))

in say cell F3, entered using Ctrl-Shift-Enter instead of Enter. Copy the formula down for as far as you like, to cover the maximum possible results of unique items from the list.

You could also use a similar formula to get labels for the horizontal part of your table. Then you can use my original formula to get the results you want.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.