How to Combine Variable-Length Data from Multiple Columns into One List
Question details
The user needs to consolidate sample identifiers and analyte values spread across multiple uneven columns into a single, cohesive, and sorted list.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Restructuring a scattered dataset where identifiers and values have different row lengths into one continuous, blank-free summary table.
- Observed behavior
- Data requires advanced dynamic array functions to dynamically trim ranges, filter specific columns, carry over keys downward for empty cells, and combine the outputs into a sorted, two-column list.
Verify that your spreadsheet application is updated to support modern dynamic array functions such as TOCOL, SCAN, LAMBDA, and LET. If certain bleeding-edge functions like TRIMRANGE are unavailable in your version, standard range definitions can be substituted.
Use Dynamic Array Formulas (LET, TOCOL, and SCAN)
Implement a comprehensive array formula to dynamically map variables, filter empty rows, carry identifiers down, and sort the consolidated output into a unified array.
This method leverages the LET function to assign names to calculation results, keeping the formula organized and efficient. It uses TOCOL to flatten the multi-column layout into one column, while SCAN paired with LAMBDA fills in the missing identifier keys for rows that only contain analyte values.
Identify the full range that holds your sample keys and analyte data. For this example, let's assume the data is located in A3:K10000.
Select an empty destination cell where the new list should begin. Begin typing `=LET(rng, TRIMRANGE(A3:K10000,2,0), ...)` to establish the main data range as a variable named 'rng'. Note: If TRIMRANGE is unsupported, substitute it simply with `A3:K10000`.
Add variables inside LET to isolate the target values and ignore blanks. Use `val, FILTER(DROP(rng,1), TAKE(rng,1)="Analyte")` to get your data columns, and define a lambda function `fnλ, LAMBDA(arr, TOCOL(IFS(val<>"", arr), 2))` to flatten arrays vertically.
Incorporate the SCAN function to carry the latest sample key downward across empty cells: `key, fnλ(SCAN("", TAKE(DROP(rng,1),,1), LAMBDA(a, v, IF(v="", a, v))))`.
Finalize the LET formula by combining the generated keys and values using HSTACK, and sorting them with SORTBY. The final line looks like: `SORTBY(HSTACK(IF(key=DROP(VSTACK("", key), -1), "", key), fnλ(val)), key, 1, fnλ(SEQUENCE(, COLUMNS(val))), 1)`. Press Enter to generate the spilled array.
Combine Complex Data Columns Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, enabling you to effortlessly restructure, sort, and combine complex variable-length datasets without needing to write macros or VBA scripts.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the multiple columns of data you want to combine.
- 2. Select a clear destination range: Click on a single empty cell with plenty of empty rows and columns below and to the right so the array has room to spill.
- 3. Apply the dynamic array formula: Paste the LET, TOCOL, and SCAN formula provided above into the formula bar.
- 4. Press Enter to execute: Hit Enter. WPS Spreadsheet will dynamically compute and output the fully combined and sorted data list instantly.
Frequently Asked Questions
Why am I getting a #SPILL! error when trying to combine the data?
A #SPILL! error occurs when the dynamic array formula tries to expand to display the combined list, but there is existing data in one or more cells within the spill range. Clearing the cells below and to the right of your formula will immediately resolve the issue.
What is the purpose of the TOCOL function in this formula?
The TOCOL function takes an entire array or multi-column range and flattens it into a single column. When combined with a function like IFS or its built-in ignore parameter, it becomes an extremely efficient way to consolidate scattered data while omitting blank spaces.
Can I perform this operation using Power Query instead of complex formulas?
Yes. If your version doesn't support functions like LET or TOCOL, you can use Power Query. Select your data, click 'From Table/Range', select the identifier columns, and choose 'Unpivot Other Columns' to easily flatten the data. However, the array formula method is preferred if you need real-time, instantaneous updates when source data changes.
Why does the formula return a #NAME? error?
A #NAME? error indicates that the software does not recognize one of the functions in the formula. Ensure your version of Excel or WPS Spreadsheet supports the functions you are trying to use. Functions like TRIMRANGE are part of newer Beta/Insider updates. Replacing it with standard range references (e.g., A3:K10000) often fixes this issue.
