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

mickyd67

Board Regular
Joined
Jul 13, 2011
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top