Stef Salvez
2012-Jul-03 18:29 UTC
[R] 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
Petr PIKAL
2012-Jul-04 07:36 UTC
[R] A challenging question: merging excel files under a specific pattern
Hi Well, this is help list for R not for Excel, maybe you shall contact Microsoft guys. I believe that probably easiest would be to make a simple macro in Excel. If you want to do merging in R you shall go through help pages for read.xls, merge, cbind, rbind and R data import/export manual. Regards Petr> > 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 > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guidehttp://www.R-project.org/posting-guide.html> and provide commented, minimal, self-contained, reproducible code.