How to Arrange Duplicate Supplier Codes Across Columns in WPS Spreadsheets
Question details
The user needs to restructure a two-column supplier/code dataset so each unique supplier appears once with all associated codes placed horizontally in the same row.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A supplier column contains repeated supplier names and a related code column contains multiple codes; the goal is a flattened, one-row-per-supplier layout.
- Observed behavior
- The current data places each supplier-code pair on its own duplicate row, while the required output places each supplier's codes across adjacent columns in one row.
Make sure the supplier and code columns are adjacent and contain no merged cells. Check that supplier names are identical across duplicate rows because extra spaces or inconsistent capitalization can split what should be one grouped supplier.
Use UNIQUE, FILTER, and TRANSPOSE to Put Each Supplier's Codes in One Row
Use this method when you want the fastest formula-based grouped layout in WPS Spreadsheets and your version supports dynamic array functions.
The formula first lists all unique suppliers, then filters the codes belonging to the current supplier and transposes them horizontally.
Leave the destination cells to the right empty so the dynamic array can spill without a #SPILL! error.
Click an empty cell, such as D2, type =UNIQUE(A2:A100), and press Enter. WPS Spreadsheets fills the unique supplier names down from D2.
Click the cell next to the first unique supplier, such as E2, type =IFERROR(TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2)),""), and press Enter.
Select E2, move the pointer to the fill handle at the bottom-right corner, and drag down until the last unique supplier row.
Select the result range, then use Home > Font, Home > Borders, and Home > Column Width to make the one-row-per-supplier layout easier to read.
Build a Single Dynamic Grouped Report with REDUCE, LAMBDA, VSTACK, and HSTACK
Use this advanced method when you want one formula to create the entire supplier-code matrix without helper columns.
Arrange Duplicate Supplier Codes in One Row with WPS Spreadsheets
WPS Spreadsheets gives you a free, formula-based way to transform duplicate supplier rows into a clean one-row-per-supplier layout. Use UNIQUE, FILTER, TRANSPOSE, and LAMBDA helper functions while keeping full .xlsx compatibility with Microsoft Excel.
- 1. Open the workbook in WPS Spreadsheets: Download and install WPS Office, open the .xlsx file that contains the supplier names and codes, and confirm the data starts in two adjacent columns.
- 2. Apply the grouping formula: Click the cell where you want the unique supplier list, enter =UNIQUE(A2:A100), and in the next column enter =IFERROR(TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2)),"").
- 3. Fill and format the grouped table: Drag the fill handle down for all unique suppliers, then use the Home tab to add borders, adjust column widths, and apply bold headers.
Frequently Asked Questions
Why does the TRANSPOSE(FILTER(...)) formula return #SPILL! in WPS Spreadsheets?
The destination range is not empty. The formula needs enough blank cells to the right for each supplier code. Clear the adjacent cells and re-enter the formula; if you still see #SPILL!, check for hidden content or merged cells in the spill area.
What WPS Spreadsheets version is required for UNIQUE, FILTER, and TRANSPOSE?
These are dynamic array functions available in current versions of WPS Office. If typing =UNIQUE returns #NAME?, update WPS Office from the official WPS website, then reopen the workbook.
Can I use this method if my supplier and code data is in an Excel Table?
Yes. Click inside the data, press Ctrl+T to create a table, then use structured references such as =UNIQUE(Table1[Supplier]) and =TRANSPOSE(FILTER(Table1[Code],Table1[Supplier]=D2)). WPS Spreadsheets keeps the formulas updated when rows are added.
How do I convert the formula results to static values before sharing with other Excel users?
Select the resulting cells, press Ctrl+C, then right-click and choose Paste Special > Values. This makes the one-row-per-supplier layout compatible with older Excel versions that do not support dynamic arrays.
Does this formula handle suppliers with no codes?
Yes. Use the IFERROR wrapper shown in the recommended solution, =IFERROR(TRANSPOSE(FILTER($B$2:$B$100,$A$2:$A$100=D2)),""), to return a blank instead of an error for suppliers with no matching codes.
