WPS Spreadsheet: Convert Vertical Excel Records into a Two-Column Table
Question details
The user needs to extract alternating IDs and properties from a vertical list and place them into two separate columns.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An Excel-compatible list contains an ID in one row, its property in the next row, and this pattern continues vertically. The user wants an ID and Property table.
- Observed behavior
- IDs and properties remain combined in one vertical range instead of being arranged as matching records in two columns.
Identify the first cell in the alternating list and confirm whether the first item is an ID. Select an output area with two empty columns so the formulas can spill without overwriting existing data.
Use FILTER formulas to split alternating records into two columns
Use this solution when IDs and properties alternate consistently from the first row of the source range.
The first formula returns every other value starting with the first row, while the second returns the values in between. Replace B1:B10 with the actual source range and adjust the starting row when necessary.
In WPS Spreadsheet, click the first cell of the output ID column, such as D1.
Enter =FILTER(B1:B10,MOD(ROW(B1:B10)-ROW(B1),2)=0) in D1, then press Enter to spill the IDs downward.
Click the first cell of the adjacent Property column, such as E1, enter =FILTER(B1:B10,MOD(ROW(B1:B10)-ROW(B1),2)=1), and press Enter.
Confirm that each ID in column D aligns with its corresponding property in column E. Change B1:B10 to match the complete source range if records are missing.
Arrange matching groups with dynamic array functions
Use this approach when the source data is organized into separate matching groups rather than one simple alternating list.
Split Alternating Excel Records in WPS Spreadsheet
WPS Spreadsheet can use Excel-compatible dynamic array formulas to separate alternating IDs and properties into aligned columns. Its familiar formula bar and spill results make it easy to transform vertical records without manually copying each value.
- 1. Open the source workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the vertical records.
- 2. Create the ID column: Click an empty output cell and enter a FILTER formula that returns every other value from the source range.
- 3. Create the Property column: Click the adjacent output cell and enter the matching FILTER formula for the alternating values.
- 4. Review and save the table: Check that each ID matches its property, then use File > Save As to save the result as an XLSX or other required format.
Frequently Asked Questions
How do I split an alternating list into IDs and properties in WPS Spreadsheet?
If the list starts with an ID in B1, enter =FILTER(B1:B10,MOD(ROW(B1:B10)-ROW(B1),2)=0) in the ID output column and =FILTER(B1:B10,MOD(ROW(B1:B10)-ROW(B1),2)=1) in the adjacent Property column.
What should I change if my source list starts in B2 instead of B1?
Use B2 as the first reference in both the range and row calculation, such as =FILTER(B2:B10,MOD(ROW(B2:B10)-ROW(B2),2)=0). This treats the first value in B2 as the first ID.
Why are my IDs and properties not aligned after using FILTER?
Check that the source range begins with an ID, contains no blank or extra rows, and covers the entire list. A missing value changes the alternating pattern and shifts the results.
Can I use HSTACK, TAKE, DROP, and TOCOL for this task?
Yes, but those functions are better for data arranged in separate matching groups. For a simple alternating vertical list, FILTER with MOD is the more suitable formula.
