Forum Discussion

Sajal Roychowdhury's avatar
Sajal Roychowdhury
Copper Contributor
Dec 05, 2017

Data Filling in Transpose

Hello ,

      I have a data sheet which consists of date in Month-Year Format from 2015 to 2029. I have a column which has Project Names in it (Total 83) .

 

I want the data to be transposed so that every Month -Year has the  Project Name with it  .

 

Like this :

AAC  Feb-15
AAC  Mar-15
AAC  Apr-15
AAC  May-15
AAC  Jun-15
AAC  Jul-15
AAC  Aug-15
AAC  Sep-15
AAC  Oct-15
AAC  Nov-15

 

Original Data is in this form :

AAC  Jan-15 Feb-15 Mar-15
AET  Jan-15 Feb-15 Mar-15
CP Jan-15 Feb-15 Mar-15
DD Jan-15 Feb-15 Mar-15
DDC  Jan-15 Feb-15 Mar-15

The date are stretching till Dec-29

Can we do this ? 

excel
Formulas and Functions
Macros and VBA

3 Replies

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Dec 05, 2017

    Hi Sajal

    If you have Excel 2010 or later then Power Query is perfect for this

     

    You turn your original data into a Table or a named range then use Power Query to "unpivot" it. 

     

    Let me know what version of Excel you're on and if you've used Power Query before and I can give you some guidance.

     

     

     

    • Sajal Roychowdhury's avatar
      Sajal Roychowdhury
      Copper Contributor
      Dec 06, 2017

      Hello Wyn ,

                Thanks for the response . I am currently using Excel 2016 64 bit version . Haven't tried the Power Query . Can you provide some guidance into this ? 

       

      Regards,

      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Dec 06, 2017

         

        Step 1: Convert table using Ctrl + t

         

         

         

         

        Step 2: Data > From Table

         

         

         

         

        Step 3: Delete Change Type Step

         

        Step 4:  Right Click on First Column and select Unpivot Other Columns

         

         

         Step 5:   Remove the Attribute Column (via right click)

         

         

        Step 6  Click on ABC123 and chose Date

         

         

        do the same on the Project column and choose Text

         

        Step 7: Rename Columns as Project and Date

         

         

         

        Step 8:  Change Query Name to something meaningful, then click Close and Load To...

         

        Choose Table,  and the converted data will be loaded back into Excel

        Demo File Attached

Resources