How to Create a Dynamic Item and Warehouse Summary from Two Excel Tables in WPS Office
Question details
Create a dynamic item and warehouse summary that combines ordered and available quantities from two source tables and includes only GS Storage and Blank locations.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two source tables with order and availability data and needs a consolidated report grouped by item and warehouse.
- Observed behavior
- The expected output is a dynamic summary showing summed ordered and available quantities for GS Storage and Blank warehouse locations only.
Arrange the two source tables in the same workbook with consistent column headers for item, warehouse, ordered quantity, and available quantity. Close any filters that could hide source rows before loading the data into Power Query.
Use Power Query to append the tables, filter locations, and group by item and warehouse
Use this if the source data will grow or change and you want a refreshable summary in WPS Spreadsheets.
Power Query lets you combine both tables into one query, keep only GS Storage and Blank locations, then group and sum quantities without changing the source ranges.
Select a source table range, go to Data > From Table/Range (or Data > Get Data > From Table/Range) in WPS Spreadsheets, check My table has headers, and click OK.
In the Power Query editor, click Home > Append Queries > Append Queries as New, select the two table queries, and click OK to create a combined query.
Click the filter arrow on the Location column, choose Text Filters > Equals, select GS Storage and Blank, and click OK.
Click Home > Group By, set Group by to Item and Warehouse, add two aggregation columns for Ordered Qty and Available Qty, choose Sum, and click OK.
Click Home > Close & Load > Close & Load To, choose Table and a new worksheet, then click OK.
Use dynamic array formulas with FILTER, UNIQUE, and SUMIFS
Use this when you prefer a formula-based summary that updates automatically without opening Power Query.
Create a Refreshable Item and Warehouse Summary in WPS Office Spreadsheets
Use WPS Office Spreadsheets to combine two tables, filter to GS Storage and Blank, and group by item and warehouse so ordered and available quantities are summed in a dynamic report.
- 1. Download and install WPS Office: Go to the official WPS Office website, download the free installer, and sign in or skip to open WPS Spreadsheets.
- 2. Open the two source Excel tables: Launch WPS Spreadsheets, open the workbook, and make sure both source tables are in the same file with consistent headers.
- 3. Append and filter the tables: Use Data > From Table/Range or Data > Get Data > From Table/Range to load both tables into Power Query, append them, and filter the Location column to GS Storage and Blank.
- 4. Group and sum the quantities: In the Power Query editor, use Home > Group By to group by Item and Warehouse and sum Ordered Qty and Available Qty.
- 5. Load and refresh the summary: Load the grouped result to a new worksheet as a table, then use Data > Refresh All to update it when the source data changes.
Frequently Asked Questions
Can I use a PivotTable instead of Power Query for this item and warehouse summary?
Yes. Combine the two tables into one range, insert a PivotTable, drag Item and Warehouse to Rows, Ordered Qty and Available Qty to Values, and filter Location to GS Storage and Blank.
What should I do if my source tables have different column names?
Rename or map the columns to consistent names before appending in Power Query. Ensure the Location, Item, Warehouse, Ordered Qty, and Available Qty fields line up between the two tables.
How do I keep the summary updated when new rows are added?
For Power Query, click Data > Refresh All after adding rows. If you use formulas, convert the source data to Excel Tables and reference the table columns so the ranges expand automatically.
Why does my formula return 0 for some item-warehouse combinations?
Check that the location filter includes both GS Storage and Blank and that the item and warehouse spelling and spaces match exactly. Use TRIM or clean the source column if necessary.
