Collating multiple Excel files into one Excel file

Question

I've team wise reports in .xlsx files that look like following. The files names are like team1.xlsx, team2.xlsx ... teamN.xslx

Date  | Report
-------------
2-Oct | Stuff 1
3-Oct | Stuff 2

Now I want to collate all the info into another Excel file that looks like

Date  | Team 1  |  Team N
--------------------------
2-Oct | Stuff 1 | Stuff N1
3-Oct | Stuff 2 | Stuff N2

Is this possible and if so how?

TIA

Answer 1

See the following page on SO:

Merge Excel Files Into One.

Answer 2

Since these are text files, you could use a few Cygwin or Gnu32 command line utilities to create a single text file of the following format:

Team   | Date  | Report
-------------------------
Team 1 | 2-Oct | Stuff 1
Team 2 | 3-Oct | Stuff 2
...
Team N | 2-Oct | Stuff N1
Team N | 3-Oct | Stuff N2

Then import the new text file into Excel and then create a Pivot Table to reorganise it the way you want.

Answer 3

In the files team1.xlsx etc B2 to BN cells contain the data

I discovered that the data from another xlsx file maybe got as follows:

='C:\PathToFile\[TeamN.xlsx]Sheet1'!B2
='C:\PathToFile\[TeamN.xlsx]Sheet1'!B2
...
='C:\PathToFile\[TeamN.xlsx]Sheet1'!BN

Thanks all anyway!

BTW if the question/subject wasn't clear feel free to edit for others!