Forum Discussion
Combining multiple quarterly workbooks into one master workbook.
Thanks for your answer! Macros seem to be blocked by my IT department, so I will see if I can make the power query method work - the tutorial you linked looks very helpful.
I have never used power query before - Will I be able to use it even if the columns are not consistent? As in, if there is a new column in the latest workbook, will it just create a new column in the file and have null values for the previous rows?
Cheers,
Brian
You may create template file with dummy data with all possible columns in your files and, if columns have names (texts in first row before data), with exactly the same names as in your files. Combining the files take that template as the sample.
If in one of the files compare to the template extra columns appear, or column(s) have another order, or column(s) have another names - such data will be missed or you'll have an error, depends on situation.
if in the file there are no some columns as in template, data will be filled by null.
SergeiBaklan and nabilmourad,
Thanks for your replies!
So if make a template file with all the columns and headers that will be there in the end, it will add null data to columns where there is no data and add the data it has to the appropriate column?
For instance if I make a table template that looks like this:
Col A Col B Col C Col D Col E Col F Col G And my first worksheet looked like this:
Col A Col B Col D Col E Col F abc abc abc abc abc And a second looked like this:
Col A Col B Col C Col E Col G abc abc abc abc abc I would get an output like this?
Col A Col B Col C Col D Col E Col F Col G abc abc abc abc abc abc abc abc abc abc And if so, would it matter if the columns weren't necessarily in the same order as long as they had the same headers?
Cheers,
Brian
And attached are template and two dummy files. Put them into the folder c:\test (only these files, nothing more), file from previous post in any other place and be sure all together works. After that you may try to play with your real files in similar way.
Thanks for posting the files. Unfortunately I don't have access to my C:/ drive... Will it work if I put it in my z:/ drive? Or would the code need to be changed?
Cheers,
Brian
Hi Brian,
Yes, practically. Initial result will be as
After filtering the template file and removing first column
You may add as many files as needed into the folder