MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have around 200 worksheets in separate .xls files which all have the same column titles and I want to combine them into 1 Excel file and into 1 worksheet so I can use pivot table to analyse them. Is there a 'quick' way of doing this? Or what is the best way of doing this? My current method of opening them all and moving them into a new worksheet is very longwinded. Thanks
Import with SAS/R, combine and export for pivot table analysis in XL. Are the names similar to allow for recursive coding?
It's doable in VBA but if you don't know vba it'd be quicker to hand crank it - assume you can chuck them all in one folder then iterate through those and pull the data onto a single sheet, and that there won't be too many rows for a single sheet once you have combined them all?
Data / Get Data / From File / From Folder.
If the file names are all fairly similar then a VBA can loop through them one by one and copy and paste the data into the collated file.
Yeah you could write a script for it in Python / R / Matlab. It's the sort of thing I'd waste an hour doing when Cmd+A, Cmd+C, Alt-Tab, Cmd-V *200 could do it in about 10 minutes.
Thanks for the tips folks.
Power query add in would do this easily.