How to gather/summarize data over many identically formated sheets to summary arrays. Two types
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.