Combining excel workbooks with same header columns but various rows in a master wb

0
votes

Hello I am trying to create a 'master' sheet that houses data from 4 workbooks. All four workbooks have the same headers but they are for different regions. I.E data for the east,west,north and south. Each workbook has a different amount of rows. I want to automate the process of combining the documents into one.

I tried power query but i'm not a expert.

1
excelpowerquery
One option is to create linked tables in Access to your 4 workbooks. Then you can easily combine them with a union query and send the results back out to a master workbook. - K753
myexcelonline.com/blog/… - Tim Williams
trumpexcel.com/combine-data-from-multiple-workbooks - Tim Williams

1 Answers

0
votes

The steps in Power Query:

  • in a new workbook, create a query to each of the four workbooks
  • add a column for the region with the formula ="Region A (adjust the region name accordingly.
  • load the queries as connections only, not to a worksheet
  • in one of the four queries, append the other three.
  • load that one to a worksheet.