Question reagarding putting together various Excel sheets

Question

Dear all,&nbsp;&nbsp;Perhaps one of you may help me with a (seemingly easy) topic. However, looking twice, this is not a trivial problem (at least for me...).&nbsp;I would like to put together the information from various Excel sheets in one general Excel sheet.&nbsp;Pls mind that I don't want to consolidate. Instead, what I am looking for is just a way to automatically put together the lines from various excel sheets in one general sheet. I do want to avoid a "manual" copy-paste, though.&nbsp; &nbsp;&nbsp;Assume the following simple example. I would like to put together the contents of my ExampleExcelsheets 1, 2 and 3 to get the ExampleExcelsheet "All"&nbsp;In each sheet the tables have identical structures (i.e. identical columns). In each sheet, however, are various numbers of rows (i.e. cases).&nbsp;&nbsp;&nbsp;ExampleExcelsheet1Column1&nbsp; Column2 Column3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;ExampleExcelsheet2Column1&nbsp; Column2 Column3b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp;ExampleExcelsheet3Column1&nbsp; Column2 Column3c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp;---------------------------After putting together the following Excelsheet should result:&nbsp;ExampleExcelsheetAllColumn1&nbsp; Column2 Column3a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; b&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;c&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; d&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp;----&nbsp;You might think that this is a quick copy-paste thing, and that is certainly true. However, imagine you have hundreds of excel-sheets and want to put them all together. In all of these hundreds of sheets, the structure of the tables is identical, but there are dozens of columns and different numbers of&nbsp; lines. Thus, it becomes a difficult task to put them all together. Assume now, you have to do this work once a week....&nbsp;Does anyone have an idea how this tedious work can be done automatically?&nbsp;Many thanks for your valuable input!&nbsp;Ronald&nbsp;(Berlin, Germany)&nbsp;&nbsp;

hansvogelaar · Answer

Ronald_Freytag&nbsp;
Are all those worksheets stored in a single workbook? If so, are there sheets that should be skipped?
&nbsp;
Or are they stored in separate workbooks? If so, do all workbooks reside in a single folder?

mtarler · Answer

Ronald_Freytag&nbsp; this sounds like a great application for Power Query.&nbsp; If you aren't familiar maybe take some time to learn about it because even if it doesn't help in this case (which I doubt) it will likely help you in other cases.&nbsp; Here is a link to a good intro (click on each chapter to go through the tutorial):https://exceloffthegrid.com/power-query-introduction/&nbsp;&nbsp;

ronald_freytag · Answer

HansVogelaar&nbsp;&nbsp;Dear Hans,&nbsp;all the sheets are in one file.However, some irrelevant sheets (that should not be taken into account) might be there.&nbsp;Kind regardsRonald&nbsp;&nbsp;

ronald_freytag · Answer

mtarler&nbsp;&nbsp;Hi,Thanks a lot, this does indeed seem to be very helpful.&nbsp;As you rightfully assumed, I am not familiar with this stuff, but I spend some time to read the help section. And yes, this is what I need.&nbsp;&nbsp;Many thanks again!&nbsp;&nbsp;&nbsp;&nbsp;

ronald_freytag · Answer

HansVogelaar&nbsp;Hans,Many thanks, I just got an answer to my question.&nbsp;&nbsp;&nbsp;

Forum Discussion

Question reagarding putting together various Excel sheets

5 Replies

Resources