Table of Contents
Download your Exercise file by clicking the button below
The Work Situation – Graphically
If you have multiple tables in a single workbook where the tables have the same structure with different values coming from different regions. You may want to combine all the tables together in a new worksheet in the same workbook. We can do that by using Power Query, VBA, Office script, but in this article I will show you how to do that using either Dynamic Array function or Python.
Let me know which method you prefer.
The Source Data
Combining Data with Python
Python is a popular programming language that is now integrated withing Excel.
- Define a Data Frame that stores each table
- Define a Data Frame that combines the 3 other frames
- Append the data.
In a new worksheet select cell A1 and then click Python on the Formulas tab
- Alternatively use the shortcut CTRL + ALT + SHIFT + P
- To use the defined frames in a Python function can only be done to the left or below the cell where I define the Frame.
Type (either in A1 or in the Formula Bar):
- I selected the tables one at a time by clicking at the upper left corner of each table (black rotated arrow). A single click selects the data only, while 2 clicks select the data and headers.
- I select the data and headers only for the first table (to avoid duplicate headers)
- If the data is in a list, then I select any cell and hit CTRL + A
- In a Python code, I move from one line to the next line by hitting Enter
- We combine the 3 tables in another variable named “Frames”
- The last line will store the result of appending the 3 tables using the concat function from the Pandas library.
- To commit a Python function we hit CTRL + Enter
- You get a Python object as a result. To switch to Excel Values click on the drop list to the left side of the formula bar and select Excel Values,or, use the shortcut CTRL + ALT + SHIFT + M
- Because my data is in a table format ► I do not get duplicate headers. But in case of lists we can remove the Header rows from the second and third lists by using a Tail function:
If you get an extra column of “Index” you can remove it by adding one more line to the end of the code:
Hit CTRL + Enter to commit the Python function, and the data is combined.
Dynamic Array Function
- Tables are named with a pattern
- A VSTACK Function
- To select a Table with headers: Click twice in the upper right corner
In a New worksheet, in Cell A1
Create a VSTACK function and select the 3 tables separated by commas.
To select a Table without headers: Click once in the upper right corner
To select a Table with headers: Click twice in the upper right corner
When hitting Enter the function spills to adjacent cells and the tables are combined.
- Since the Data is in a Table format, adding more records will be automatically added to the combined report.
- However, adding more Tables requires editing the function.
Let Know which method you prefer
Watch me doing it on YouTube