How to create Pivot Table from multiple SAP worksheets?

How much time do you take to aggregate 20 SAP reports into a Pivot Table Report?

Alice is a supply chain manager who keeps track of the stock inventory of the components for each project on a weekly basis. Each week, she tracks the stock inventory of 20 projects. To get the details, she requests for the project reports from another department. The department will then generate the SAP reports, download into Excel and send them to her.

If it is organized in proper rows and columns, it wouldn’t be a problem for Alice. But the downloaded SAP file will come in the following layout and she has to extract the details from each project file.

In each SAP report, there are 4 columns Alice has to extract. They are Material, Quotn qty, Dlv qty and Undlv qty. And she needs to do the same for the other 19 reports and create a Pivot Table from them.

Are you able to recommend a good Excel solution to Alice? She spends more than an hour doing this before she can analyze them.

If you would like to see how I did it, click on the button to sign up. I will be doing a live demonstration on 31 Aug 2021 @ 11am (SGT). It’s free!