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...
excel
Formulas and Functions
Macros and VBA
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