+ Reply to Thread
Results 1 to 12 of 12

Merging multiple .xls files

  1. #1
    Registered User
    Join Date
    04-14-2011
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Merging multiple .xls files

    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.
    Last edited by Tim@Work; 04-20-2011 at 12:57 PM.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Merging multiple .xls files

    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

  3. #3
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Merging multiple .xls files

    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
    Alan


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    04-14-2011
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Merging multiple .xls files

    Quote Originally Posted by tigeravatar View Post
    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.

    Quote Originally Posted by tigeravatar View Post
    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.

    Quote Originally Posted by tigeravatar View Post
    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.

  5. #5
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Merging multiple .xls files

    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
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-14-2011
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Merging multiple .xls files

    Alan,

    You have a PM

    Tim

  7. #7
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Merging multiple .xls files

    Tim
    Responded in PM
    Alan

  8. #8
    Registered User
    Join Date
    05-09-2011
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Merging multiple .xls files

    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?

  9. #9
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Merging multiple .xls files

    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

  10. #10
    Registered User
    Join Date
    04-14-2011
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Merging multiple .xls files

    Alan,

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

  11. #11
    Administrator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,748

    Re: Merging multiple .xls files

    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

  12. #12
    Registered User
    Join Date
    04-14-2011
    Location
    Beaverton, Oregon
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Merging multiple .xls files

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1