How to summarize 52 weeks worth of data into one workbook.

[KLWX2250]

Hello everyone,

I am an excel newbie that needs some serious help! I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.

Here is what 1 week's worth of data looks like...

Here is what the 52 week summary page looks like...

And here is what the summary page looks like with all of the data on it....

Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.

I tried searching around the forums to try and find an answer that worked in this situation but nothing seemed to work after multiple hours of experimentation.

I greatly appreciate any feedback on this issue.

 

[ArthriticPanda]

Hi,

I would consolidate your data into a single sheet and then use a pivot table to create a dynamic report

e.g.

Excel Workbook
	A	B	C	D
1	Week	Company	Carloads
2	1	Company A	15
3	1	Company B	5
4	1	Company A	4
5	1	Company B	7
6	1	Company C	12
7	1	Company B	9
8	1	Company A	5
9	1	Company C	10
10	1	Company A	2
11	1	Company B	13
12	1	Company A	8
13	1	Company B	10
14	1	Company A	12
15	1	Company C	4
16	1	Company A	6
17	2	Company A	12
18	2	Company B	4
19	2	Company A	7
20	2	Company B	9
21	2	Company C	14
22	2	Company B	16
23	2	Company A	23
24	2	Company C	7
25	2	Company A	4
26	2	Company B	13
27	2	Company A	9
28	2	Company B	10
29	2	Company A	12
30	2	Company C	4
31	2	Company A	2
Sheet1

Excel Workbook
	A	B	C
1	Week	1
2
3	Row Labels	Sum of Carloads
4	Company A	52
5	Company B	44
6	Company C	26
7	Grand Total	122
8
Sheet4

This will allow you to manipulate the data rather than try and search through a huge set of numbers.

Hope this helps,

AP

 

[KLWX2250]

Hi,

I would consolidate your data into a single sheet and then use a pivot table to create a dynamic report

e.g.

This will allow you to manipulate the data rather than try and search through a huge set of numbers.

Hope this helps,

AP

Although I totally agree with you, it's the consolidation part that I am having trouble with. Since there are 52 tabs (weeks) to get data from it takes forever to copy and paste from each one. So, if you or someone else knows of a way to consolidate all of this data in a timely fashion that would be great. We have to be able to see every week individually on one big worksheet so we can go back and figure out why one week was different than the others.

I appreciate your assistance!

KW

 

[ArthriticPanda]

I see your problem. If all your data is the same layout i.e. Company is in B and Carloads in G then a simple macro should read all the sheets and copy it over. I'll write a script for you which should cover it and get back to you.

AP

 

[KLWX2250]

I see your problem. If all your data is the same layout i.e. Company is in B and Carloads in G then a simple macro should read all the sheets and copy it over. I'll write a script for you which should cover it and get back to you.

AP

That would be very helpful and much appreciated! All of the carload data is as you described with companies on column B and carload data in G. Let me know if you need anything else

 

[ArthriticPanda]

I've created a workbook for you and sent you a PM explaining things.

 

[KLWX2250]

I've created a workbook for you and sent you a PM explaining things.

Thank you, I will go read it now. Sorry for not responding sooner, we've had a few clients in town and it's taken up a ton of my time. I'll definitely let you know if this works!