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 (Highlighted in Red) and create a summary. Kindly support 

 

 

Expected Output

 

 

Thank you in advance

 

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

9 Replies

Sort By
  • 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)

Resources