'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Transpose Excel Data Horizontally and Remove Duplicates
Question details
The user needs to convert vertically structured data into horizontal columns while filtering out repeated lot numbers, and requires alternative methods for older software versions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing vertical data layouts into a horizontal format and filtering out duplicate lot number entries for cleaner reporting.
- Observed behavior
- Attempting to use modern dynamic array formulas like TOROW and FILTER returns a 'That function is not valid' error in older spreadsheet versions.
Verify your current software version by navigating to File > Account, as the most efficient formula methods require Microsoft 365 or Excel Online.
Use Dynamic Array Formulas (TOROW and FILTER)
The most efficient method for Microsoft 365 and Excel Online users to instantly filter and transpose data with a single formula.
Dynamic array formulas automatically spill the results into adjacent empty cells, making data reorganization seamless. This method is highly recommended if you are using a modern version of the software.
Click on the empty cell where you want the transposed horizontal data to begin.
Type the formula =TOROW(FILTER($B$2:$E$9,$A$2:$A$9=G2),,0) into the formula bar, adjusting the cell references to match your actual data ranges.
Press Enter to execute the formula. The data will automatically filter out the specified lot numbers and spill horizontally across the columns.
Use Power Query for Older Versions
A robust alternative for users on older desktop versions that do not support dynamic array functions.
Use Paste Special with Manual Deduplication
A quick, manual method suitable for one-off tasks in any version of the software.
Use WPS Spreadsheet to Transpose Data and Filter Duplicates
WPS Office offers a feature-rich Spreadsheet application that provides advanced data tools, comprehensive formula support, and seamless format conversions—completely free of charge. You can easily clean up duplicate entries and transpose your layouts without compatibility headaches.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your vertical data and lot numbers.
- 2. Remove duplicates: Select your data range, navigate to the Data tab, and click the 'Remove Duplicates' icon to instantly clean the dataset.
- 3. Copy the data: Highlight the deduplicated vertical data and press Ctrl+C to copy it to your clipboard.
- 4. Transpose horizontally: Right-click the target cell, select 'Paste Special', check the 'Transpose' option, and click OK to convert the data horizontally.
Frequently Asked Questions
Why do I get a 'function is not valid' error when using TOROW or FILTER?
This error occurs because older versions of Excel (such as Excel 2016 or 2019) do not support modern dynamic array functions. You need Microsoft 365 or Excel Online to use functions like TOROW, TOCOL, and FILTER.
How do I check which version of Excel I am currently using?
Open your spreadsheet application, click on the File tab in the top-left corner, and select Account (or Help in older versions). Your exact version and build details will be displayed under the Product Information section.
Can I transpose data using a PivotTable?
Yes, PivotTables are excellent for grouping and transposing data in older versions. Insert a PivotTable, drag your Lot Number field into the Rows area to automatically group duplicates, and place the data fields you want to transpose into the Columns or Values areas.
