+ Reply to Thread
Results 1 to 2 of 2

Pivot table using multi-value column

  1. #1
    Registered User
    Join Date

    Pivot table using multi-value column

    I trying to build a pivot table using a column that has multiple values separated by a comma. For each register (line) this column could have different values in different quantities. Something like:

    XXXXX YYYYY valueA, valueB, valueC
    KKKKK ZZZZZ valueD
    HHHHH TTTTT valueB, valueC

    My question is: is there any way to have the pivot considering each value of the last column as a separated one? In other worlds, how can I transform the table above in something like this:

    XXXXX YYYYY valueA
    XXXXX YYYYY valueB
    XXXXX YYYYY valueC
    KKKKK ZZZZZ valueD
    HHHHH TTTTT valueB
    HHHHH TTTTT valueC

    Thank you and best regards,
    R. Inomata
    Please Login or Register  to view this content.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    Essex, UK
    MS-Off Ver

    Re: Pivot table using multi-value column


    To generate the Pivot you will need to first transform the table as you have outlined.

    Applying "Text to Columns" on your concatenated value column (based on comma) will get you part of the way but you would not want to use the results of this as the source for the Pivot as you will have multiple amount columns.

    However, once you have run Text to Columns you can then in turn using an intermediate Multi Consolidation Pivot to help you transform the data set - see: http://datapigtechnologies.com/blog/...-a-pivottable/

    The alternative would be to use VBA to do the transformation (ie alter original table) which if this task is to be repeated would make sense.

    If it's a one off task I'd simply follow the manual process given it won't take long.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1