Excel- I have 2 wb's each with different data on same Co's- how do I find data from 2nd wb on Co from 1st wb?

Question

I have a large Excel 2007 spreadsheet which has 40 workbooks. In each workbook are rows of many companies with several columns of data for each company. The companies are the same in each workbook, and the columns are the same, but the data changes from workbook to workbook. Essentially, each workbook represents what the companies looked like at 40 different points in time.

I need a single report which filters a small number (say 4) of the companies from each workbook (same combination of filters for each workbook) and presents data for them from the following workbook.

Thanks

Answer 1

I would suggest you consolidate all the worksheets into one sheet. Since each worksheet has the same data, add one more column that specifies the time/date per row.

Once everything is in this format, you can build a pivot table to filter by company & measurement.

Answer 2

i think of the uncomplicated logic must be to study the two contraptions of information into arrays. Then examine to to work out in the event that they the two have the comparable style of things (i think of the function is UBOUND). in the event that they have the comparable style of things, then style (look on the information superhighway for workouts to style) and loop for the duration of the arrays using a FOR assertion. some thing like: theyAreTheSame = actual FOR i = one million to UBOUND(arrayOne) IF (StrComp(arrayOne(i), arrayTwo(i), one million) < 0) THEN theyAreTheSame = fake go out FOR end IF next

Answer 3

you just need to reference the worksheet in the formula. For example:

=Sheet2!a1+Sheet3!a1

This will add cell A1 from sheet2 and cell a1 from sheet3. Of course, if your worksheets aren't named "Sheet2", "Sheet3" you'll need to use the actual names.