A challenging question:merging excel files under a specific pattern

A challenging question: merging excel files under a specific pattern

Dear all,

I have an excel file that contains 6 sheets

1,2,3,4,5,6

The analysis is repeated every 3 sheets

Sheets 1, 2, 3:

I want to add (horizontally) the data contained in the matrix : sheet2 (5:end,3:end )

of *Sheet2 * to the sheet3 such that the first element of the matrix *sheet2 (5:end,3:end ) *

to occupy the location/cell sheet3(5,end+1 ) of sheet3.

Say, that the output from this merging is sheetA. Then I want to add horizontally the data contained in the matrix : Sheet1 (5:end,3:end ) of Sheet 1 to the sheetA * such that the first element of the matrix *sheet1 (5: end,3:end ) to occupy the location/cell sheetA(5,end+1 ) of sheetA.

As you can see

1)I add sheet2 (5:end,3:end ) * to *sheet3 at location *sheet3(5,end+1) *

2) then I add sheet1 (5:end,3:end ) to the output sheetA that results from the merging of sheets 2 and 3 at location sheetA((5,end+1).

3) The output is named ,say, sheetAA

Similarly analysis holds for the other block of sheets 4,5,6. That is,

Sheets 4, 5, 6:

1)I add sheet5 (5:end,3:end ) to sheet6 at location sheet6(5,end+1)

2) then I add sheet4 (5:end,3:end ) to the output sheetB that results from the merging of sheets 5 and 6 at location sheetB((5,end+1).

3) The output is named, say, sheetBB

In my case I have a large sequence of sheets that I have to merge in this way. That is,

1,2,3,4,5,6,7,8,9,10,11,12,13,…

But the logic is the same as described above.

Is there any “easy” way to do that kind of merging? . Because when you have 13*3 =39 sheets is a bit tedious to do that merging manually.

thanks

Answer 1

[Kevin Claytor]

Sure, use xlsread to load the data, operate on it and use xlswrite to write the file back. For example, something like;

xlfilename = 'myhugeexcelfile.xls'
finaldata = [];
for ii = 1:nsheets
  data = xlsread(xlfilename,ii); % Reads in sheet ii
  finaldata = [finaldata;data];  % appends it to the data
end
outfile = 'processedexcel.xls'
xlswrite(outfile,finaldata);     % write the data to a new file

You'll have to tweak it to your needs, but this should get you started.

[Sabbas]

thanks Kevin. I will tweak it to my needs and get back to you if I have a problem. Thanks for your help!

[Sabbas]

thanks to you the problem can be solved easily

many thanks

[Image Analyst]

If you want to get it done sooner than eternity, use ActiveX instead of xlsread and xlswrite.