locked
Dynamic pivot/unpivot of data RRS feed

  • Question

  • Hi,

    I have a list of data (document number, amount, date) per purpose number that I would like to change to look like a pivot table but with the amount and document number per month.

    Purpose No           Date           Document Number        Amount

    8909801            Apr 2017          JNL123                      1,200

    8909801            May 2017         JNL124                       1,000

    8909802             Apr 2017         JNL125                       8,000

    This output must look like follows:

    Purpose No                              Apr 2017_Doc   Apr 2017_Amount       May2017_Doc  May2017_Amount       Total

    8909801                                  JNL123                   1,200                  JNL124                      1,000              2,200

    8909802                                  JNL125                   8,000                                                                         8,000

    I've concatenated the doc no & amount together; pivoted the columns; unsplit each column again into doc no & amount.  I works fine, except if there is a new month.  Then the column is not automatically split and renamed.  I was wondering if I can select all the columns at once and then unsplit them. 

    Tuesday, February 6, 2018 11:11 PM

Answers