'%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 Combine Duplicate Values into Another Column in Excel
Question details
The user needs to group duplicate values in one column and merge their corresponding text or numbers into a new, separate column.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating datasets where multiple rows share the same identifier but have different related values that must be combined into a single cell.
- Observed behavior
- Duplicate identifiers are grouped together, and their associated values are concatenated into a single cell in a corresponding column.
Verify your Excel version before proceeding, as modern functions like GROUPBY and FILTER are only available in Microsoft 365 and Excel 2021 or later, while older versions will require traditional array formulas.
Use the GROUPBY and TEXTJOIN Formula (For Newer Excel Versions)
This is the most efficient method for Microsoft 365 users, utilizing the modern GROUPBY function to instantly consolidate data in a single step.
The GROUPBY function, combined with LAMBDA and TEXTJOIN, can dynamically scan your range, find duplicates, and merge the associated values without needing helper columns.
Click on an empty cell (e.g., C2) where you want the new grouped output to begin.
Type the formula: =GROUPBY(A2:A6,B2:B6,LAMBDA(a,TEXTJOIN("",TRUE,a)),,0)
Press Enter. This formula groups the values in column A and combines the corresponding values from column B into a spilled array.
Use TEXTJOIN with IF (For Compatible Excel Versions)
A highly reliable array formula approach for Excel versions that support TEXTJOIN (Excel 2019 and later) but lack the newer GROUPBY function.
Use CONCAT and FILTER Formulas
Leverages dynamic arrays to quickly filter related values and concatenate them without the complexity of LAMBDA functions.
Easily Consolidate Duplicate Data with WPS Spreadsheet
WPS Office offers robust formula support, allowing you to combine duplicate records and manage large datasets efficiently. It fully supports advanced text and array functions, providing a seamless data processing experience.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your duplicate records.
- 2. Select the target cell: Click the cell where you want the combined text or numbers to appear.
- 3. Enter a combination formula: Input the TEXTJOIN and IF array formula or the FILTER formula to map out your specific ranges.
- 4. Apply and drag: Press Enter to evaluate the formula, then use the fill handle to apply it to the remaining rows in your column.
Frequently Asked Questions
Why is my TEXTJOIN formula showing a #NAME? error?
This error occurs if you are using an older version of Excel (like Excel 2016 or earlier) that does not support the TEXTJOIN function. You will need to upgrade your software, use WPS Office, or rely on a VBA macro to combine text.
Do I need to press Ctrl+Shift+Enter when using these combination formulas?
In newer versions of Excel that support Dynamic Arrays, simply pressing Enter is sufficient. However, if you are using older versions with the IF array method, you must press Ctrl+Shift+Enter to evaluate the array properly.
How do I separate the combined values with a comma instead of having no space?
You can change the delimiter in the first argument of the TEXTJOIN formula. For example, instead of using TEXTJOIN("",TRUE,a), change it to TEXTJOIN(", ",TRUE,a) to add a comma and a space between each combined value.
Is it possible to combine duplicate values without using complex formulas?
Yes, you can use Power Query as an alternative. Select your data, go to the Data tab, click 'From Table/Range', group the data by your identifier column, and use a custom column to combine the specific rows.
