How to Pivot Poorly Structured Excel Data Using LAMBDA and PIVOTBY
Question details
The user needs to normalize and restructure dataset rows containing multiple delimited categories, correctly allocate value amounts, and summarize the data using dynamic array formulas.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Transforming rows with grouped or delimited text categories into a properly expanded dataset, apportioning values across the new rows, and generating a pivot table automatically with modern array formulas.
- Observed behavior
- The original dataset is improperly structured with multiple categories merged into single rows, which prevents direct pivoting without a complex transformation pipeline to expand rows and apportion values.
Ensure your spreadsheet software is updated to support modern dynamic array functions like LAMBDA and PIVOTBY, or be prepared to use standard data cleaning tools like Text to Columns as an alternative.
Use TOCOL and LAMBDA for Dynamic Data Normalization
Build a dynamic array formula using LET, LAMBDA, and TOCOL to unpivot delimited strings, expand corresponding rows, and pass the normalized data directly into PIVOTBY.
This approach avoids complex iterative row-by-row functions by unpivoting the entire table at once. It calculates the number of categories per row, splits them, and divides the amounts equally before feeding everything into the PIVOTBY function.
Use the LET function to define variables. Start by calculating the number of items in the delimited category string using LEN and SUBSTITUTE to count the delimiters.
Split the delimited strings into an array using TEXTBEFORE, TEXTAFTER, and SEQUENCE functions to separate each item.
Define a custom LAMBDA function within your LET statement to broadcast the other columns (like Region or Order Date) using TOCOL, ensuring they match the new expanded category rows.
Divide your original values (e.g., Amount) by the category count calculated in step 1 to allocate the totals correctly across the new rows.
Nest the normalized arrays inside the PIVOTBY function. Specify your expanded row fields, column fields, the apportioned values array, and the aggregation method (e.g., SUM).
Normalize Data Using Power Query
If PIVOTBY is not supported in your current environment, use Power Query to unpivot your dataset and load it into a standard PivotTable.
Easily Normalize and Pivot Data in WPS Spreadsheet
WPS Spreadsheet provides powerful data transformation tools, robust dynamic array support, and intuitive PivotTables to help you handle poorly structured datasets without complex coding.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your poorly structured data.
- 2. Clean and normalize the data: Highlight your data and use the built-in Text to Columns feature under the Data tab to split merged categories, then copy down the corresponding row data.
- 3. Apportion values: Create a simple helper column to divide your row totals by the number of new categories to ensure accurate data allocations.
- 4. Insert a PivotTable: Select your newly structured data range, navigate to the Insert tab on the ribbon, click PivotTable, and drag your fields into the Rows, Columns, and Values areas.
Frequently Asked Questions
What is the PIVOTBY function used for?
PIVOTBY is a modern dynamic array function that allows you to create pivot table-like data summaries directly within a formula using arrays, eliminating the need to insert a traditional PivotTable object.
How does the LAMBDA function help with poorly structured data?
The LAMBDA function lets you create custom, reusable functions directly within a cell. When cleaning data, it can modularize complex row-by-row tasks like expanding delimited strings, making your formulas easier to read, test, and maintain.
Why do I need to apportion amounts when unpivoting categories?
If a single row contains a total amount (e.g., $100) but applies to multiple categories, failing to apportion it will duplicate the $100 across the newly expanded rows, falsely inflating your grand total. Dividing the amount by the category count ensures reporting accuracy.
Can I normalize data without using complex dynamic arrays?
Yes. If you prefer not to use LET, LAMBDA, and PIVOTBY formulas, you can achieve the exact same result by using Power Query to split columns by delimiter into rows, or by using standard Text to Columns and manually unpivoting the data.
