Excel - Combining Multiple Files into 1 Master

Martynt74

Martynt74

Martynt74

Soldato
Joined
20 Feb 2004
Posts
22,938
Location
Hondon de las Nieves, Spain
Is there a quick/easy way to do this?

I'm about to be sent around 200 worksheets, all with varying numbers of rows. The worksheet also has a few redundant rows at the top so the actual column headers start around row 7 and then the detail below that.

I vaguely recall an old boss having a macro set up, but this was for predefined named files. These will all be uniquely named and not something i'll know until i get them. Don't know if there's a way to just say "merge all files in folder X"

Would also need a column to with the source file name included.

Kind of hoping this is possible as otherwise i'm estimating around 2 days of work to merge them all manually! Although it feels like anything automated is going to need an element of opening each file to make changes etc so i might as well just do copy and paste if i'm doing that.

EDIT - Just to add that this is a one off piece of work, no need for it to be recurring or to monitor the source files for changes.
 
Last edited:
How big are the files? You say the data starts at row 7, so they're all standardised/consistent?

Powerquery should work (data > get data > from file > from folder) and is relatively easy figure out - https://www.youtube.com/watch?v=a7E29H5ZUmE

I had a similar problem in a previous job (except 1000+ workbooks on a monthly basis) and I couldn't actually get powerquery to work so ended up using a macro to import everything into an access database. Sometimes excel just doesn't cope well with that much data.
 
montymint said:
use the trial of something like this:

https://www.ablebits.com/excel-suite/index-2020.php

https://www.ablebits.com/office-add...s-into-one/#merge-multiple-workbooks-into-one
Click to expand...

cheers. I saw that but it looked like it kept individual sheets. I need all 200 workbooks collated onto one sheet.

I’ll look at Powerquery. I had seen that through a bit of googling but I wasn’t sure if it was just as quick doing it manually.

On the plus side it’s something for my dad who said he’d expect it to take around 6 days and will pay me for 6 days work!
 
Marvt74 said:
I’ll look at Powerquery. I had seen that through a bit of googling but I wasn’t sure if it was just as quick doing it manually.

On the plus side it’s something for my dad who said he’d expect it to take around 6 days and will pay me for 6 days work!
Click to expand...
Having a single query for an entire folder is obviously going to be quicker. As said, it's not too difficult to figure out given how powerful a tool it is. Things like needing a column with the source file name is no problem.

Obviously it depends what's in the files but 6 days seems an overestimate. For ~1000, with several hundred thousand rows, it used to take someone 4-5 days to manually copy the data out.
 
Is it simple data, all with the same column headers? If so, have them save them as CSV.
Store them all in the same folder, then open a CMD prompt:
Code: 
copy *.csv filename.csv
Open the resulting file, select column A, click Find & Select > Go to Special and select Blanks [OK]
Once they're all selected, click your Delete Sheet Rows option. After that, you should be able to CTRL-A and format as a table, sort on column A, and get rid of all duplicate header information.
 
You must log in or register to reply here.
Back
Top Bottom