How to Reshape Excel Data Using SCAN, GROUPBY, and PIVOTBY Functions
Question details
The user needs to restructure un-normalized band combinations and feature data into organized layouts using modern array formulas.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Transforming non-standard data tables into grouped or pivoted layouts directly via formulas without manual copying or pasting.
- Observed behavior
- The source data is not arranged in a normalized format, so the arrays must be reconstructed in memory before generating the final outputs.
Ensure you are using a recent version of Excel or WPS Office that supports modern dynamic array functions such as LET, SCAN, GROUPBY, and PIVOTBY. Check your source table headers and exact column references before building the formulas.
Reconstruct and Group Data using SCAN and GROUPBY
Use the SCAN function to carry category labels down the rows and GROUPBY to summarize the data into a structured layout.
When dealing with un-normalized data, you often have headers mixed into the same column as the data points. By combining LET, SCAN, and TEXTAFTER, you can create virtual arrays in memory that correctly associate each row with its proper category.
Click an empty cell where you want the new layout to spill. Type =LET( to begin your formula. Define the starting text strings you need to search for, such as bwcs, "BandwidthCombinationSet:".
Define your 'band' array by using the SCAN function to carry the band label down empty or intermediate rows: band, SCAN("", Table1[Column1], LAMBDA(a, v, IF(LEFT(v,16)="Band Combination", v, a))).
Define your 'sets' array using TEXTAFTER to pull the set names cleanly: sets, IF(LEFT(Table1[Column1],24)=bwcs, TEXTAFTER(Table1[Column1], bwcs), "").
Filter out the blank rows and use the GROUPBY function to aggregate the arrays. Your final line in the LET function should look like: result, GROUPBY(band, sets, ARRAYTOTEXT, , 0, , rptfilter).
Create a Pivot Layout using PIVOTBY
For cross-tabulated outputs where you need categories on both axes, use PIVOTBY after filling down the relevant set values.
Handle Advanced Data Reshaping with WPS Spreadsheet
WPS Office supports powerful data handling and dynamic array functionality. You can write complex formulas to analyze and restructure large datasets smoothly without relying on expensive software subscriptions.
- 1. Open your dataset: Launch WPS Spreadsheet and open the .xlsx file containing your un-normalized data.
- 2. Access the Formula tab: Navigate to the 'Formulas' tab on the top ribbon and explore the 'Insert Function' menu if you need to reference specific dynamic array syntax.
- 3. Write your array formula: Select the target cell and type your LET function in the formula bar, defining your SCAN and data grouping variables.
- 4. Generate the output: Press Enter to instantly execute the dynamic array and spill the reshaped data matrix directly into your worksheet.
Frequently Asked Questions
What does the SCAN function do in Excel?
The SCAN function applies a custom LAMBDA calculation to each item in an array and returns an array of successive results. It is highly useful for filling down values, carrying forward categories, or calculating running totals.
Why am I getting a #NAME? error when using GROUPBY or PIVOTBY?
The #NAME? error typically means your version of the spreadsheet software does not support these specific functions yet. GROUPBY and PIVOTBY are recent additions; ensure you are on the latest Microsoft 365 update or using a supported build of WPS Office.
How is TEXTAFTER different from standard string functions like RIGHT or MID?
TEXTAFTER dynamically returns the text that occurs after a specified character or substring (like a delimiter). This removes the need to manually calculate complex string lengths with FIND and LEN combinations.
Can I use multiple dynamic array functions together?
Yes. Functions like SCAN, FILTER, and TEXTAFTER are frequently nested inside a LET function. This lets you clean, fill, and rebuild data arrays step-by-step in memory before passing them to a final layout function like PIVOTBY.
