Power Query Problems

How to Consolidate Rows with the Same Control Number in Excel

Steve K Oct 19, 2026 869 views

Question details

The user needs to combine multiple rows of data sharing the same Control Number into a single row, transforming description values into column headers and displaying their respective quantities beneath them.

How to Consolidate Rows with the Same Control Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing a dataset where items have multiple parts or descriptions spread across several rows, requiring a reshaping of the data so each control number forms a single row with descriptions transposed as columns.
Observed behavior: The data currently lists multiple rows for the same control number, requiring manual grouping and transformation to view all associated parts and quantities on a single line.

Before you start

Ensure your source data is formatted as an official Excel Table (press Ctrl+T) and contains consistent column headers without any blank rows or merged cells.

Solution 1Recommended

Use Power Query to Pivot Columns

Power Query is the most efficient and automated way to pivot your dataset, turning description rows into column headers while grouping by the control number.

By utilizing the 'Pivot Column' feature in Power Query, you can dynamically reshape your dataset. This prevents the need to manually copy, paste, and transpose data range by range.

Import Data into Power Query

Select any cell inside your data table, navigate to the 'Data' tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.

Select the Column to Pivot

In the Power Query Editor, click on the header of the 'Description' column to highlight it.

Pivot the Column

Navigate to the 'Transform' tab and click 'Pivot Column'. A dialog box will appear.

Configure Pivot Settings

In the Values Column dropdown, select your quantity column (e.g., 'QTY_FT'). Click on 'Advanced options' and ensure the Aggregate Value Function is set to 'Sum', then click 'OK'.

Load the Consolidated Data

Go to the 'Home' tab and click 'Close & Load'. The consolidated dataset will appear on a new worksheet.

Automated Updating: Whenever you add new rows to your source data table, simply go to the 'Data' tab and click 'Refresh All' to update the consolidated results instantly.

Solution 2

Use a Pivot Table for Visual Consolidation

If you prefer not to use Power Query, a standard Pivot Table can visually group the control numbers and pivot the descriptions into columns.

Consolidate and Pivot Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet offers powerful data management tools, including advanced Pivot Tables, to help you quickly summarize and consolidate rows by control number without manual data entry.

1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your multi-row dataset.
2. Insert a Pivot Table: Select your data range, navigate to the 'Insert' tab, and click the 'PivotTable' button.
3. Configure the Layout: Drag your Control Number and related static fields into the Rows box, Description into Columns, and Quantity into Values.
4. Adjust to Tabular View: Use the PivotTable Options to change the display layout to Tabular form, perfectly mimicking the consolidated row setup you need.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Create and customize Pivot Tables with a familiar drag-and-drop interface.Lightweight and fast, handling complex datasets and calculations with ease.

microsoft office alternative - wps office

Frequently Asked Questions

How do I update the consolidated data when I add new rows?

If you used Power Query, simply navigate to the 'Data' tab in Excel and click 'Refresh All'. The query will automatically pull in the newly added rows and output the updated consolidated table.

Why do I get errors when pivoting the Description column in Power Query?

This usually happens if there are null values, typos in the description column, or if the values column contains text instead of numbers. Ensure your Quantity column is formatted as a numeric data type before attempting to pivot.

Can I consolidate rows without using Power Query or Pivot Tables?

Yes, you can use dynamic array formulas like UNIQUE to extract a distinct list of control numbers, and SUMIFS to pull the corresponding quantities for specific descriptions. However, this method requires more manual setup for the column headers.

What happens if multiple rows have the exact same Control Number and Description?

When pivoting the column in Power Query or using a Pivot Table, the aggregate function (such as 'Sum') will automatically add the quantities together. If you chose 'Don't Aggregate' in Power Query, duplicate intersections will result in an error.

Excel & Data Problems