Forum Discussion

Chris525's avatar
Chris525
Copper Contributor
May 20, 2024
Solved

Merge Excel workbooks and create a summary

HI All,   Looking for merging the excel files for different vendors and create one summary file.   I have 30 different excel files with the same format and want to merge a small portion (Highligh...
excel
Macros and VBA
  • Lorenzo's avatar
    Lorenzo
    May 21, 2024

    Hi Chris525 

    You're welcome. Glad I could help

    When you have a minute could you click on Mark as response (at the bottom of each reply you get here) to help people who Search - Thanks

Lorenzo's avatar
Lorenzo
Silver Contributor
May 20, 2024

Hi Chris525 

 

For this kind of things it's better to share a sample file as it's not clear if the info. you want to extract is in the first 7 rows. Assuming it's in the first 7 rows of each 'Sheet1':

  1. Store your 30 files in a folder
  2. Download & open the attached file
  3. In sheet PARAM replace 'C:\Lz\Downloads...' with the path to the folder containing your 30 files
  4. Switch to sheet Summary
  5. Right-click in the blue table > Refresh
  • Chris525's avatar
    Chris525
    Copper Contributor
    May 20, 2024

    Lorenzo 

     

    Many thanks for the quick respond.

     

    Yes ! it's in the first 7 rows of each 'Sheet1'.

     

    While refreshing I'm getting an error. Can you kindly check?

     

     

     

    Could you please share the steps how to do this merge? I like to learn.

     

    Thanks

     

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 20, 2024

      Hi Chris525 

       

      I cannot determine the source of the error with a screenshot only. What I shared is done with Power Query (aka Get & Transform). I need to know on which query step you get the error:

      1. Go to Excel Data tab > Queries & Connections
      2. Double-click on 'SuppliersSummary' in the Queries & Connections pane (Power Query Editor will open)
      3. In APPLIED STEPS (on the right) click each step, one by one, until you get the error and give me the step name on which you get the error

      (I might ask you to share your 30 files as the source of the error is due to at least one of them)

      • Chris525's avatar
        Chris525
        Copper Contributor
        May 20, 2024

        HI Lorenzo 

         

        Herewith sharing the sample files.

         

        https://www.dropbox.com/scl/fo/rjchcyyq98sa5mo195c71/AHjB_sbMHjN39V2qy0ysDWA?rlkey=n8rff3hjxvk32ia3k4s0oe4a9&st=r55ggkq5&dl=0

         

         

         

         

         

        Thanks

         

         

         

         

         

Resources