VSTACK or PYTHON for Combining Tables

VSTACK or PYTHON for Combining Tables

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.

Work Situation

In our work situation, we have a workbook with 3 sheets.

In each sheet I have some data in a table format. The tables are named East, West and North.

Data in the 3 sheets has the same structure with consistent headers.

In another worksheet, I will use Python to combine the data from the 3 sheets.

Concept:

  • Define a Data Frame that stores each table
  • Define a Data Frame that combines the 3 other frames
  • Append the data.

Steps:

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):

Df1=xl(“EastT[#All]”)

Df2=xl(“WestT”)

Df3=xl(“NorthT”)

Frames=[Df1,Df2,Df3]

Df= pd.concat(Frames)

Notes:

  • 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:

Df1=xl(“EastT[#All]”)

Df2=xl(“WestT”)

Df3=xl(“NorthT”)

Frames=[Df1,tail(Df2,-1),tail(Df3,-1)]

Df= pd.concat(Frames)

If you get an extra column of “Index” you can remove it by adding one more line to the end of the code:

Df.reset_index(drop=True)

Hit CTRL + Enter to commit the Python function, and the data is combined.

Dynamic Array Function

Concept:

  • Tables are named with a pattern
  • A VSTACK Function
  • To select a Table with headers: Click twice in the upper right corner

Steps:

In a New worksheet, in Cell A1

Create a VSTACK function and select the 3 tables separated by commas.

=VSTACK(EastT[#All],WestT,NorthT)

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.

Notes:

  • 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

Share This Post
Have your say!
0 0

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.

Thanks for Downloading Your Gift

We have sent your quick reference guide for creating Dashboard.

Please check your inbox to receive Your Gift.

Happy Learning !!

Subscribe to our Newsletter

Receive 7 Instant Gifts when you Subscribe to my monthly Newsletter below.