The Ultimate Guide to Automating Data Consolidation with VSTACK and FILTER Functions

In Excel, data management and consolidation are very important tasks. With the introduction of new functions such as VSTACK and FILTER these functions make data merging simpler and more efficient. This article explains how to use these functions to automatically merge data from separate sheets into a master sheet.

Overview of the VSTACK function

VSTACK This function is used to vertically stack multiple arrays or ranges. Its syntax is similar to SUM other functions, so if you are familiar with SUM those functions, using VSTACK it will be straightforward.

Syntax

=VSTACK(array1, [array2], ...)

• array1: Required. The first array or range to stack.
• array2: Optional. The second array or range to stack.

Basic usage

Suppose you have multiple sheets recording Cash, Bank, WeChat, and Alipay data. You can use VSTACK the function to merge them into a master sheet.

Example 1: Merge data from multiple sheets

Suppose your sheets are as follows:

You can use the following formula to merge these data into the master sheet:

=VSTACK(A2:C4,E3:G4,I3:K4,M3:O4)

Dynamic range merging

Because the sheets record new data daily, you can set larger ranges so newly added data will be merged automatically.

Example 2: Setting a dynamic range

=VSTACK(A2:C100,E3:G100,I3:K100,M3:O100)=VSTACK('01.現金:04.支付寶'!A2:C120)

This way, newly added data will automatically be included in the master sheet.

Remove unnecessary zeros

When merging data, the master sheet may contain many zeros. You can use FILTER a function to filter out these unwanted data.

Example 3: Use the FILTER function directly

If you don't want to use helper columns, you can FILTER combine the VSTACK and FILTER VSTACK functions together:

=FILTER(VSTACK(A2:C100,E3:G100,I3:K100,M3:O100),VSTACK(B2:B100,F3:F100,J3:J100,N3:N100)0)=FILTER(VSTACK('01.現金:04.支付寶'!A2:C120), VSTACK('01.現金:04.支付寶'!B2:B120)  0)

Result of automatic merging

When you enter new data in the final sheet, the master sheet updates automatically, achieving automatic data merging.

Example 4: Automatically merge new data

Suppose you add a new record in the Cash sheet:

After you update the data, the master sheet will automatically show all the latest data without manual merging.

Additional examples

Example 5: Complex conditional filtering

If you want to show only items in the "Food & Beverage" category from the merged data, you can do this:

=FILTER(VSTACK(A2:C100,E3:G100,I3:K100,M3:O100),(VSTACK(B2:B100,F3:F100,J3:J100,N3:N100)0)*(VSTACK(C2:C100,G3:G100,K3:K100,O3:O100)="餐飲"))

Condition 1:

(VSTACK(B2:B100,F3:F100,J3:J100,N3:N100)<>0)

Condition 2:

(VSTACK(C2:C100,G3:G100,K3:K100,O3:O100)=\"餐飲\")

Conclusion

Since Excel added many new functions, data manipulation has become simpler and smarter.VSTACK and FILTER Combining functions not only simplifies the process of merging data but also improves data processing efficiency. This is undoubtedly helpful for users who need to regularly update and consolidate data.