Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
    
Note that if this kind of "merging" is to be run often, you would probably make a better use of a relational database (like MySQL or MS Access). That would allow you to store different data into different tables (~sheets) and make joins when reading instead of costly merges –  PPC Apr 16 '12 at 18:32

3 Answers 3

See the following page on SO:

Merge Excel Files Into One.

share|improve this answer

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.

share|improve this answer
    
(just text) in ".xlsx files" –  Holysmoke Oct 28 '10 at 5:12
    
@Holysmoke: OK, understand you now, but don't have a solution for you. –  Mike Fitzpatrick Oct 28 '10 at 5:31
    
NP :) Thanks anyway! –  Holysmoke Oct 28 '10 at 6:07
up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.