How to Combine PivotTable Items and Quantities into One Cell per Order
Question details
The user needs to consolidate order details so that all non-blank items and their corresponding quantities for a specific order are merged into a single text cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting raw order data or modifying a PivotTable to display one row per order, with multiple items and quantities concatenated together.
- Observed behavior
- Currently, the data spans multiple rows or columns in a PivotTable layout, making it impossible to view a clean, combined text string of items per order.
Ensure you have access to your original raw data table (Order, Item, Quantity) before creating the PivotTable, as manipulating the source data often yields the cleanest results.
Use Power Query on Raw Source Data
Bypassing the PivotTable to group and merge the raw order data using Power Query is the most robust and automated solution.
Instead of extracting data from a rigid PivotTable, using Power Query on your raw data table allows for dynamic text manipulation and grouping.
Select your raw data (Order, Item, Quantity), go to the Data tab, and click 'From Table/Range' to open the Power Query Editor.
Hold Ctrl to select both the 'Item' and 'Quantity' columns. Right-click the header, choose 'Merge Columns', and type an equals sign (=) or space as your custom delimiter.
Select the 'Order' column, click 'Group By' in the Home tab, and write a custom M code or use the UI to combine the merged text rows using a semicolon delimiter.
Once the items are combined into a single cell per order, click 'Close & Load' to output the cleanly formatted table back into your spreadsheet.
Copy PivotTable as Values and Use TEXTJOIN
If you must use the existing PivotTable, extract its values and use text manipulation formulas like TEXTJOIN to combine the data.
Merge Order Data Seamlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful text manipulation formulas, including TEXTJOIN and CONCAT, alongside versatile PivotTable features to quickly summarize and format your complex data arrays.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the raw order list or PivotTable.
- 2. Use TEXTJOIN to merge items: Type the =TEXTJOIN formula in a blank cell to instantly concatenate items and quantities, automatically ignoring blank cells.
- 3. Save and share: Save your consolidated data in the universal .xlsx format to ensure full compatibility with other team members.
Frequently Asked Questions
Can I combine items directly inside a PivotTable without copying it?
No, Excel PivotTables do not inherently support concatenating multiple row items into a single text cell. You must either use Power Query on the source data or apply formulas outside the PivotTable framework.
What is the TEXTJOIN formula used for in this scenario?
TEXTJOIN allows you to combine text from multiple ranges, insert a specific delimiter like a comma or semicolon between each, and automatically ignore any empty cells. This makes it perfect for merging sparse order data.
Why is Power Query recommended over PivotTables for text merging?
Power Query handles text manipulation and grouping directly on the raw data stream. It creates a robust, automated workflow that easily adapts when new order data is added, unlike extracting static values from a PivotTable.
