Results 1 to 4 of 4

Thread: Sumarize Column Itens to Row itens, to ease any PivotTable operation

  1. #1

    Question Sumarize Column Itens to Row itens, to ease any PivotTable operation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everybody! o/

    I remember I saw the solution of this issue in somewhere I don't remeber and can't find anymore =/


    I have this table (the months are displayed across the columns; and, as you know, it is not functional for pivotTables... )


    COD Jan Fev Mar
    A R$ 888.776,90 R$ 714.586,75 R$ 408.103,09
    B R$ 848.231,05 R$ 403.279,70 R$ 527.541,09
    D R$ 824.346,00 R$ 810.158,19 R$ 411.793,13
    C R$ 613.248,97 R$ 442.260,36 R$ 486.297,32
    C R$ 60.881,67 R$ 605.333,44 R$ 655.718,64
    B R$ 352.809,29 R$ 733.127,27 R$ 558.978,99
    D R$ 111.820,37 R$ 582.317,05 R$ 87.162,92
    C R$ 631.178,56 R$ 815.187,53 R$ 185.327,59
    D R$ 419.612,71 R$ 301.621,49 R$ 101.658,99
    C R$ 847.428,76 R$ 227.127,47 R$ 511.085,62
    B R$ 771.030,41 R$ 717.916,70 R$ 735.928,78
    A R$ 818.496,48 R$ 679.196,35 R$ 271.951,86
    D R$ 564.415,74 R$ 43.279,95 R$ 829.163,20
    C R$ 579.084,06 R$ 252.352,52 R$ 682.411,79
    B R$ 686.905,46 R$ 792.187,48 R$ 714.699,01




    So..... I want to turn the table aforementioned into this one look-alike (Oh yeah!!! know the data is organized in a way i can easily go through with the PivotTables \o/ )

    COD Month Value
    A Jan R$ 888.776,90
    A Fev R$ 714.586,75
    A Mar R$ 408.103,09
    B Jan R$ 848.231,05
    B Fev R$ 403.279,70
    B Mar R$ 527.541,09
    D Jan R$ 582.317,05
    D Fev R$ 815.187,53
    D Mar R$ 301.621,49
    C Jan R$ 227.127,47
    C Fev R$ 717.916,70
    C Mar R$ 679.196,35



    Thank you a lot!


    An example: Examplo_sumarizeColumn.xlsx

  2. #2
    Perhaps something like this? please see the file attach

    Regards
    Rizky
    Attached Files Attached Files

  3. #3
    you could unwind it in a pivot table or through power query.

    to do it in a pivot table:

    1. click in the table and hit ALT D P
    2. select multiple consolidation ranges and click next
    3. create a single page field, click next
    4. select the table including the column headers and click Add then next
    5. new worksheet....finish

    you should have a pivottable with all the data.....find the grand total value and double click it

    then just clean up the columns and change the column headers.

    you can do the same thing a little easier in power query but you'd need to install it if you don't have it.
    the instructions above should work in all versions.

    Theres a few videos on youtube on how to do it also. just search on 'Learn Excel - Unwind Pivot Data - Podcast 1787'
    it would probably be a little clearer then my simple explanation.

  4. #4
    Thank you Weazel!

    This solution is great!

    That's exactly the video of Bill Jelen that I saw and didn't remember!

    Here is the solution with PowerQuery: https://www.youtube.com/watch?v=yX-QNxaOj9c


    THanks a lot! =D

Posting Permissions

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