How to Combine Text by Matching Three Columns in Excel
Question details
The user needs to concatenate text values in a target column whenever the values across three specific grouping columns are identical within a very large dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Summarizing and restructuring a massive dataset (approximately 365,000 rows) by grouping identical records and merging their corresponding text values into single cells.
- Observed behavior
- The goal is to generate a consolidated table where unique combinations of the three grouping columns are listed once, and the associated text from the fourth column is joined together by a delimiter.
Ensure your dataset is formatted as an Excel Table (press Ctrl+T) to make referencing easier and dynamic. Since the dataset is exceptionally large, close any unnecessary background applications to free up system memory before executing complex queries.
Use Power Query to Group and Combine Text
Power Query is the most scalable and efficient tool for restructuring large datasets without freezing Excel.
Handling over 300,000 rows using standard Excel formulas can cause significant performance lag. Power Query processes data in the background, making it the ideal solution for grouping records and concatenating text in massive tables.
Click anywhere inside your data table. Go to the 'Data' tab on the Excel ribbon and click 'From Table/Range' to open the Power Query Editor.
Hold down the 'Ctrl' key and click the headers of the first three columns (the ones you want to match). Right-click any of the selected headers and choose 'Group By'.
In the Group By dialog box, set the 'New column name' to 'Combined Text'. Set the 'Operation' to 'All Rows' and click 'OK'.
In the formula bar at the top (or in the Advanced Editor), locate the grouping step. Replace the auto-generated code for the new column with: `{"Combined Text", each Text.Combine([Column4Name], ", "), type text}` (ensure you replace 'Column4Name' with your actual column header).
Click 'Close & Load' in the Home tab. Power Query will process the 365,000 rows and output a clean, grouped table in a new worksheet.
Combine Text Using Excel 365 GROUPBY Formula
For Microsoft 365 users, the new GROUPBY function combined with ARRAYTOTEXT offers a formula-based approach to group and combine text.
Try WPS Office for Efficient Data Analysis
Processing massive datasets and executing complex array formulas can slow down your computer. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Excel, ensuring excellent performance even with data-heavy files.
- 1. Download and install: Get WPS Office for free from the official website and install it on your PC or Mac.
- 2. Open your files: Open your existing .xlsx spreadsheets directly in WPS Spreadsheet without format or data loss.
- 3. Analyze seamlessly: Leverage WPS Spreadsheet's optimized calculation engine to handle large data analysis effortlessly.
Frequently Asked Questions
Why does Excel freeze when I use array formulas on 365,000 rows?
Array formulas recalculate dynamically whenever a change is made. Applying complex functions like ARRAYTOTEXT across hundreds of thousands of rows consumes massive CPU and RAM resources, which often causes the application to become unresponsive.
Can I use TEXTJOIN instead of Power Query?
Yes, TEXTJOIN can be combined with FILTER or IF statements to achieve this. However, executing TEXTJOIN array formulas over 300,000+ rows is highly inefficient and will drastically reduce your spreadsheet's performance compared to Power Query.
Is Power Query available in all versions of Excel?
Power Query is built-in natively under the 'Data' tab for Excel 2016 and newer versions. For Excel 2010 and 2013, it is available as a free add-in that must be downloaded from the Microsoft website.
How do I update my Power Query results when data changes?
Power Query does not auto-update like standard formulas. To update the grouped text, right-click anywhere inside the generated result table and select 'Refresh'. The query will automatically pull the new data and reapply the concatenation steps.
