• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a single pivot table from multiple sheets in the workbook

Hi Alan,
Please find attached the file, I have tried to create a pivot for the data in two sheets, however I cannot move the headers into rows or columns
Ex: I want the "subject" and "Name" to be placed in rows and marks in column
I have created a pivot for the data in a single sheet, and need a new pivot for the data in multiple sheet in the same format
 

Attachments

  • Book1.xlsx
    91.9 KB · Views: 4
Attached is your file with a proposed solution using Power Query. I uploaded each table to PQ. Appended one table to the other and then pivotted the data.

Data Range
A
B
C
D
E
1
Name​
Social​
Science​
Maths​
English​
2
Neel​
210​
150​
105​
255​
3
Sravs​
180​
120​
135​
285​
4
Sree​
150​
240​
165​
135​
 

Attachments

  • Book1.xlsx
    102.1 KB · Views: 4
ThnQ for the quick reply Alan :)

Did you debug your code first?http://www.cpearson.com/excel/DebuggingVBA.aspx - I could not do this as we are not supposed to download or open any links at office... My query is completely for office purpose.

About the power Query..... I have gone through the video, its really good and it is useful... Unfortunately we do not have MS office 2016 installed in office laptop.....

Please suggest any other solution....
Thank you!!
 
Power Query is available as a free add in download starting with Excel 2010. If you are using an earlier version--:( otherwise google it and download. Talk to your IT department as this is a valid add-in to excel.
 
Last edited:
Back
Top