How to Consolidate Excel Rows by SKU Using Power Query or VBA
Question details
The user needs to group product data by SKU to create a single row per item while combining related text values, such as Distribution Type, into one field.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing and cleaning product data where multiple duplicate entries exist for a single SKU.
- Observed behavior
- The current dataset contains multiple rows per SKU. The goal state is a consolidated dataset with one row per SKU and all associated text values concatenated.
Before consolidating your data, make sure to save a backup copy of your original spreadsheet to prevent any accidental data loss during transformation.
Consolidate Rows Using Power Query
Power Query is the most efficient and robust method for grouping rows and merging text fields without writing complex code.
By grouping the data by SKU, you can use a custom aggregation to combine the text values using a delimiter. This method is highly repeatable and automatically updates when your source data changes.
Select your data range, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Select the SKU column in the editor, click 'Group By' on the Home tab, set your new column name, and choose 'All Rows' as the operation. Click OK.
Click 'Advanced Editor' on the Home tab. Locate the grouping step in the code and replace the 'All Rows' aggregation logic with Text.Combine([Distribution Type], ", ") to concatenate the text fields.
Click 'Close & Load' in the top-left corner to output your successfully consolidated data into a new worksheet.
Combine Field Values Using TEXTJOIN Formula
For newer versions of Excel, combining dynamic array formulas like UNIQUE and TEXTJOIN offers a quick, formula-based solution without opening Power Query.
Automate Row Consolidation with VBA
If you frequently process specific data formats or use an older Excel version, a VBA macro using a Dictionary object can automate the entire consolidation process.
Merge Rows and Combine Text with WPS Spreadsheet
WPS Spreadsheet offers powerful data management tools, including full support for dynamic array functions like UNIQUE, FILTER, and TEXTJOIN, making it incredibly easy to consolidate duplicate SKUs without relying on complex external plugins.
- 1. Open your dataset: Launch WPS Spreadsheet and open the product data file containing the duplicate SKUs.
- 2. Find unique SKUs: Click an empty cell and use the =UNIQUE() function to instantly extract all distinct SKUs from your main list.
- 3. Consolidate text data: Next to the unique SKU, enter the formula =TEXTJOIN(", ", TRUE, FILTER(value_range, sku_range=current_sku)) to merge the corresponding text values.
- 4. Apply to all rows: Press Enter, then drag the fill handle down to consolidate the remaining text values for all your SKUs.
Frequently Asked Questions
Can I combine text in Power Query without using the Advanced Editor?
Yes. After grouping by SKU, you can extract the nested tables by expanding the column, then selecting the 'Extract Values' option from the list. It will prompt you to choose a delimiter like a comma or semicolon.
Why is the TEXTJOIN formula returning a #NAME? error?
The TEXTJOIN and UNIQUE functions are only available in Excel 2019, Microsoft 365, or modern spreadsheet alternatives like WPS Office. If you are using an older version, the software will not recognize the function.
Why should I use Power Query instead of VBA for consolidating rows?
Power Query provides a visual interface that is easier to maintain and update without programming knowledge. It also handles large datasets more safely than basic VBA macros, which require strict rules for handling duplicates and errors.
