Merging multiple .xls files

[Tim@Work]

I know it's been covered, there are a lot of pages that come up in the search and I've read through most of them, however, when I look at the code it just looks like gibberish to me. I was hoping what I needed to do would be a few clicks of the mouse but the more I read the more I realize it's a lot more complicated.

The solution to this problem is worth money to my company and priceless to my sanity. Before I go into it, we deal mainly with gas stations and I think it may be easier to understand if I paint a little picture (puts on my painting fro and gets ready to put some happy trees in). The information I need in the end is sorted first by location and second by storage tank.

Each facility has a unique ID number, this is how they are sorted in the examples. Within each facility, there are storage tank numbers... three tanks at one facility would show three of the same facility ID, name, city, state, etc. but the tank ID would be 1,2 & 3.

What I'm looking for is a solution I can copy and paste or a step by step instruction on how to get the result I need. Here are the files:

Facility
Construction
Monitoring
Piping

If the solution provided can sort the test files as needed, I'll try it on the actual files, these are 8 to 9 columns with somewhere around 244,000 lines. If that works, I'll be a very happy camper. I am willing to send PayPal, this solution is important to what I'm doing.

I have used AbleBits but the problem I run into is that while the new column is added, it doesn't add new rows to accommodate for the fact there's more information about each tank than there is for each location.

[tigeravatar]

Tim,

Couple of questions:
1) Are the files (not the sample ones, the actual ones) all stored in the same folder or network drive?
2) Will the "master file" (the one where all the data will be merged to) contain all information from the separate files? (so a column with a heading matching the heading of each separate file: piping, construction, name, address, etc).
3) Are there multiple versions of the files, or the same files simply get updated and so the "master file" would only need to pull in new updated information?

With that information I should be able to get started on something that will do what you're looking for
~tigeravatar

[alansidman]

If you have MS Access, then this is a simple case of importing each of the files to Access as a table. Join the facility table to each of the other tables with a LEFT join in a query on the Facility ID and the Tank Nr. Within a very short time, your query will join all the tables into one result. This can then be exported back to Excel for further analysis. Total elapsed time to accomplish this is a matter of minutes.

Alan

[Tim@Work]

1) Are the files (not the sample ones, the actual ones) all stored in the same folder or network drive?

They are currently in my Drop Box but I can save them locally if needed or even copy and paste so all the sheets are in one workbook in different tabs.

2) Will the "master file" (the one where all the data will be merged to) contain all information from the separate files? (so a column with a heading matching the heading of each separate file: piping, construction, name, address, etc).

Yes, I'd like to be able to merge the heading that match and add the new ones.

3) Are there multiple versions of the files, or the same files simply get updated and so the "master file" would only need to pull in new updated information?

Nope, just the individual files I have. Once they are merged into a master file it won't change. I'll just get to start the whole process over again with the next set of files.

[alansidman]

In case you were interested, here is an Access db with all your data and a query that combines it and creates a pivot table. I exported the pivot to excel for you to see what it would look like.

Alan

[Tim@Work]

Alan,

You have a PM

Tim

[alansidman]

Tim
Responded in PM
Alan

[Hankmarg]

Alan,
That looks great... I have a similar challenge and must admit i'm not sure i fully understand how to do what you did. I'm pretty proficient at Excel but not great in Access.

My issue is that i receive Daily reports in seperate Excel files, and am looking to quickly/easily merge all files for a given month into a new summary data file to facilitate month-ending analysis. So it would look like 30 or 31 individual files for each day of the month, saved in one network folder. Summary file would have same format but with all data merged in.

The data is for inventory transactions... Item Number, Description, Qty, Transaction Type, Transaction Date. I'd want to roll up all transactions by item number for the monthly period.

Any thoughts on how I could modify your solution above for this purpose?

[alansidman]

If the file layout for each is exactly the same (ie. Same number of columns--fields and each same column is formatted exactly the same), you could either do a union query in MS Query which I am not very adept at, or you could import each of the files into Access. Create a Union Query in Access which will consolidate all the files into one and export it back out to Excel. If this is something you are interested in, post a couple of small sample files and I will do this and post back with a step by step tutorial using your sample data.

Alan

[Tim@Work]

Alan,

Would it be okay if I hosted the tutorial file you sent? That would be a great help to others reading this.

[alansidman]

Tim;
That would be great. I was looking for it earlier thinking about doing that but I apparantly trashed it. Please post it. It may be best to post it in the tutorial section.

Alan

[Tim@Work]

Here's the link to the tutorial for anyone reading this wondering where the mysterious last step is we're talking about :-)

https://docs.google.com/document/d/1...thkey=CPeZ-foN

I don't change files in my Google Docs very often so these links should last a few years at least, or until the world ends and Googles servers are destroyed.