Power Query Problems

How to Filter Rows and Combine Duplicate Items in Excel

Elise WilliamsElise Williams Nov 4, 2026 870 views

Question details

The user needs to extract rows to another sheet based on text conditions (beginning with A10 or B10) and then combine duplicate item numbers while summarizing their locations and quantities across a large dataset.

How to Filter Rows and Combine Duplicate Items in Excel
Product
Excel
Device & OS
not provided
Scenario
Managing a dataset of over 13,000 rows where specific items must be filtered out and duplicate entries must be consolidated into a neat summary.
Observed behavior
Instead of relying on complex formulas to filter and combine data, the user seeks an efficient way to output the filtered rows and group the duplicates accurately.
Before you start

Ensure your dataset is organized in a proper tabular format with clear column headers (e.g., Item Number, Location, Quantity) and no blank rows before importing it into Power Query.

Solution 1Recommended

Use Power Query and PivotTables to Filter and Consolidate

This is the most efficient method for handling large datasets (like 13,000+ rows) as it automates the filtering process and summarizes data without the need for complex nested formulas.

Power Query easily handles multi-condition filtering, while a PivotTable provides a native, highly customizable way to group duplicate items and sum their quantities.

1
Load data into Power Query

Select any cell in your original dataset, go to the 'Data' tab on the ribbon, and click 'From Table/Range'. This will convert your data into an Excel Table and open the Power Query Editor.

2
Format and filter the columns

Select the 'Location' column and ensure its data type is set to Text. Next, click the drop-down arrow on column B, select 'Text Filters' > 'Begins With'. In the dialog, set the first condition to begins with 'A10', select 'Or', and set the second condition to begins with 'B10'.

3
Close & Load the filtered data

Click the 'Close & Load' button on the Home tab of the Power Query Editor. This will export your newly filtered rows into a brand new worksheet.

4
Create a PivotTable to combine duplicates

Click anywhere inside your new filtered table on the new sheet, go to the 'Insert' tab, and select 'PivotTable'. In the PivotTable Fields pane, drag 'Item Number' into the Rows area, and drag 'Quantity' and 'Location' into the Values or Rows area to consolidate duplicate entries.

5
Add a slicer for further filtering (Optional)

To easily interact with your summarized data, go to 'PivotTable Analyze' > 'Insert Slicer'. Choose categories like 'Location' to quickly filter your consolidated view.

Use Power Query and PivotTables to Filter and Consolidate
Time-Saving Tip: Once set up, if your original dataset changes, you simply need to click 'Data' > 'Refresh All' to update both your filtered Power Query table and your PivotTable instantly.
Efficient Data Processing Alternative

Filter and Summarize Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful PivotTable functionalities and advanced data filtering tools to help you manage large datasets seamlessly. Easily extract data and consolidate duplicate records in just a few clicks without relying on complex array formulas.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx file containing the 13,000+ rows.
  2. 2. Filter the rows: Navigate to the Data tab, click AutoFilter, and use the custom text filter on column B to isolate records beginning with A10 or B10.
  3. 3. Insert a PivotTable: Select the filtered data, go to the Insert tab, and click PivotTable to output the data to a new worksheet.
  4. 4. Combine duplicate items: Drag the Item Number field into the Rows section and the Quantity field into the Values section to automatically combine duplicates and sum their values.
100% compatible with Microsoft Excel formats (.xlsx and .xls)Robust PivotTable and data consolidation features for large datasetsLightweight application with fast processing for 10,000+ row tablesFree and intuitive interface with zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why is my Power Query 'Begins With' filter not allowing more than two conditions?

In the basic Power Query filter menu, you can only set up to two conditions using 'And/Or'. To add more than two 'Begins With' criteria, you must select the 'Advanced' radio button at the top of the Filter Rows dialog box. This allows you to add multiple clauses using the 'Or' operator.

Can a PivotTable combine text values, like listing multiple locations in one cell?

By default, PivotTables only sum, count, or average numeric values. They do not concatenate text. To combine text strings (like listing all locations for a duplicate item), you can use the TEXTJOIN function combined with FILTER, or utilize Power Query's 'Group By' feature with the 'All Rows' operation to write a custom column that extracts and joins the text.

Will the PivotTable update automatically if I add new rows to my original 13,000-row data?

No, PivotTables and Power Query tables do not update in real-time as you type. If you add or modify the source data, you must right-click anywhere inside the PivotTable or the Power Query output table and select 'Refresh' (or click 'Refresh All' on the Data tab) to process the new records.