Solved

How to gather/summarize data over many identically formated sheets to summary arrays. Two types

Posted on 2016-07-22
8
40 Views
Last Modified: 2016-08-31
Hi,
In the spreadsheet there are 6 similarly formatted sheets. Each sheet is named for a State.
1.  Columns A-F is a list of companies.  Columns A and B are the company ticker & name. columns C - F are stats about each company. I would like to takes these columns from the six sheets and create a new sheet that has 7 columns ( the new column being the State Name.  Once I have the summary sheet, I want  to know how to create a matrix where each row is a stock (first four columns = columns A,B,E, and F.and each column in the matrix is a State.   Column D is the data within the matrix.

2. In columns I and  J is a weights summary of each state by GSector. I woud like to aggregate these into a summary table where each column is a State and each row is one of the ten sectors.

Thanks,
Don
TestSumm1-.xlsx
0
Comment
Question by:donohara1
  • 4
8 Comments
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41725988
Your column D state names do not always agree with the state name on the tab.
0
 
LVL 7

Accepted Solution

by:
tomfarrar earned 250 total points
ID: 41726026
I think this is generally what you were asking for.  Assumed the tab names were correct, and completed the data.  You can move the data around as you want in the pivot tables shown in the red tabs.
State.xlsx
0
 

Author Comment

by:donohara1
ID: 41726507
HI Tom,
Yes, thanks for this.  
Sorry about the State names. I had extracted the 5 columns from my master book which has, so far, 51 tabs, and that will expand in the future.

I see you used Pivot tables.  Nice to see the menus in Excel 2013.

What I have to learn is the data gathering techniques. How did you assemble the two tables. I plan to do this for workbooks with many sheets (51 to 300).  In the past I have tried, unsuccessfully, to modify from recorded Macros. Please let me know more.

Thanks,
Don
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 7

Expert Comment

by:tomfarrar
ID: 41726604
Okay, I didn't realize there were going to be many tabs.  I did it with a few copies and paste.  There is a consolidate worksheet option within Excel, but I have not used it much.  So that is an option, and I believe you could find video on youtube if you looked.  I will do the same when I have time.

I do have a macro, provided to me from an Expert here, that does that, but I need to find it.  I'll look later on today.  A possible hurdle could be that each tab has two tables, the detail and the summarized version.  Can the summary on the right of each tab be calculated from the detail (I didn't look at it that closely)?  If so, perhaps you don't need it, or if you do need it, can the data be separated from the detail worksheet?
0
 
LVL 7

Expert Comment

by:tomfarrar
ID: 41726618
Sorry I am not looking at the data as I write this, but there is the issue of getting the state name from the tab to the column, unless you have another way of doing that.  Is this how the data comes to you?
0
 
LVL 21

Assisted Solution

by:Ejgil Hedegaard
Ejgil Hedegaard earned 250 total points
ID: 41729639
Try attached.
All sheets with not coloured tabs are assembled on sheet TotalList, and result used in the 2 pivot tables on Detail and Summary.
Press Update button to run the macro,
TestSumm2.xlsm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now