An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
How to gather/summarize data over many identically formated sheets to summary arrays. Two types
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
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
Who is Participating?
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
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
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?
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?
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?
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
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
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
State.xlsx