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

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

     

     

     

     

     

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

      Chris525 

       

      I cannot repro. the error with the 3 sample files you shared:

       

      However, I made a couple of changes to the query trying to guess where the issue comes from. Could you try with the updated version attached please?

      If you still get the same error please share all your files

       

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

        Hi Lorenzo 

         

        Its working now. Thank you so so much.

         

        I tried to understand the power query steps, but couldn't get it 100%. Is it possible to explain what are the steps your followed please.

         

        Thanks

Resources