How to Move Repeated Vertical Excel Matches into Horizontal Columns in WPS Office Spreadsheet
Question details
The user needs to reorganize a two-column worksheet where pupil names repeat vertically and each pupil has a varying number of email addresses, so each pupil appears once per row with email addresses spread across horizontal columns.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- A teacher or administrator has a list of pupils in one column and email addresses in another, with multiple rows per pupil, and needs a one-row-per-pupil layout.
- Observed behavior
- Pupil names repeat vertically according to how many email addresses each pupil has, and the email addresses are currently stored vertically in a separate column.
Make sure your data has consistent headers and no completely blank rows inside the list. If you plan to use dynamic array formulas, verify that your WPS Office Spreadsheet version supports UNIQUE and FILTER functions before applying them.
Create unique pupil rows and spill email addresses horizontally with UNIQUE, FILTER, and TRANSPOSE
Use this method when your WPS Office version supports dynamic array formulas; it automatically updates when source data changes.
This method places a unique pupil list in an empty column and then uses a dynamic array formula to spill each pupil's email addresses horizontally. The result updates automatically whenever the source data is edited.
Open your workbook in WPS Office Spreadsheet, then click a blank cell where the unique pupil list should begin, for example D2.
Type =UNIQUE(A2:A100) into D2 and press Enter. The unique pupil names spill down from D2 automatically.
Click E2 and enter =TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2,"")) and press Enter. The email addresses for the pupil in D2 spill horizontally.
Select E2, drag the fill handle down alongside the unique pupil list to apply the formula to the remaining pupils.
Use a Remove Duplicates list and IFERROR, INDEX, SMALL formulas for older versions
Use this if your WPS Office version does not support dynamic array spill functions; it combines Data > Remove Duplicates with a manual array formula.
Simplify vertical-to-horizontal data reshaping in WPS Office Spreadsheet
Use dynamic array formulas in WPS Office Spreadsheet to automatically turn repeated vertical rows into one horizontal row per pupil, without manual cutting and pasting.
- 1. Download and install WPS Office: Go to the WPS Office download page, install WPS Office, and open WPS Spreadsheet.
- 2. Open your Excel workbook: Click Menu > Open, choose the workbook that contains the repeated pupil and email address columns, or simply double-click the .xlsx file.
- 3. Apply the dynamic array formulas: Select a blank cell, enter =UNIQUE(A2:A100) and =TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2,"")) as described above.
- 4. Review the horizontal results: Adjust column widths and confirm each pupil's email addresses display in one row.
Frequently Asked Questions
Which WPS Office versions support UNIQUE and FILTER?
Recent WPS Office Spreadsheet versions support dynamic array functions. To check, type =UNIQUE(A2:A100) in a blank cell; if it returns #NAME?, update WPS Office or use the INDEX/SMALL method described in this article.
How do I make the formula update automatically when new pupils are added?
Use a larger source range, such as A2:A1000 or an Excel Table, so UNIQUE, FILTER, and TRANSPOSE automatically include new rows when data is appended.
Can I open this workbook in Microsoft Excel after using WPS Office?
Yes. WPS Office Spreadsheet saves in Excel-compatible .xlsx format, so the formulas and spilled horizontal results remain usable in Microsoft Excel.
What should I do if my WPS Office returns #SPILL!?
Clear the cells to the right of the formula. Dynamic array formulas need empty adjacent cells to spill the horizontal email addresses, so remove any data or formatting blocking the spill range.
How do I display a blank instead of zeros for pupils with no email?
Wrap the formula in IFERROR(...,"") or use the FILTER empty-string argument shown in this article, so no matching addresses produce an empty string instead of an error value.
