Huge amount of data across dozens of files - which are updated weekly - and I want to consolidate each week

[mickyd67]

OK, here goes...

I have supply & demand information that is managed by a team - which generates around 50 individual reports on a weekly basis across all the suppliers we work with.

My first step is to standardise all those reports so the data is at least structured the same across each file. The full supply chain data is copied into sheet 1, from SAP - and there are several pivot tables run, in sheet 2, to pull only the data we need out of the first sheet. SAP spits out a tonne of data we don't need - and I can't do anything about it.

One of the first challenges: because each supplier can have a different amount of items the full supply chain data can be anything from 5,000 to 50,000 lines of data. That's making the standard file size very large because each pivot table is looking at a range of up to 50,000 rows - across 50+ columns for weeks of data.

First question would be: can a pivot table adjust itself automatically depending on the amount of rows populated on a page? Rather than you choosing the range manually.

= = =

I am then creating sheet 3 which is pulling information from the various pivot tables into a supply / demand calculation page. This runs from left to right, and it's pretty horrible to navigate

= = =

Sheet 4 has been created to pull out the relevant detail in sheet 3 into a much easier to read format - this is basically the item number / description & 52 columns showing the supply position in any given week.

= = =

I want the data from Sheet 4, across the ~50 files, to be pulled into one single file so I can get a fully supply-chain overview

The files names change each week - i.e. Wk01 supply & demand / Wk02 supply & demand etc.

So if I am using something like 'get data' how does it account for all the file names for the~50 files changing as each week passes?

Or is there anything better to use than 'get data'?

= = =

Hope that explains it - always find it difficult to try and spell out what I am trying to do with this type of thing :-/

Thanks all
mickyd

 

[HighAndWilder]

Re: First question would be: can a pivot table adjust itself automatically depending on the amount of rows populated on a page? Rather than you choosing the range manually.

If you use a dynamic named range or base the pivot table on an actual Excel table then when the Pivot Table is refreshed it will take into account all of the
rows required.

You may want to think about agregating or summarising the data before you pivot it.

As far as copying data from 'Sheet 4" in multiple workbooks is concerned :

Are the files all in one folder?
Are there any other files with the same file extension in the folder?
Is there a file naming convention?
Do you have a list of the source files and their folder/s?
Do all of the source worksheets have the same column headings with the data in the same order?

Answers to the above will determine some of the various options available to you and identify any issues that may need to be resolved.

 

[mickyd67]

Hi there - thanks for the reply.

In the past few days I've managed to get my head around Power BI and I am using that to pull data across into a central file. I do have some challenges there mind you, but I am going to start a new thread on that.