How to Move Duplicate Student Email Data into One Excel Row
Question details
The user needs to consolidate duplicate student records by merging a second email address into the primary student's row and deleting the remaining duplicate rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a student contact list where some students have multiple rows for different email addresses, with the goal of combining them into a single row per student.
- Observed behavior
- Student records are split across multiple rows due to multiple email addresses, requiring a method to merge the data onto one row and remove the redundant records.
Ensure your dataset is sorted by the student identifier (such as Student ID or Name) so that duplicate records appear in directly adjacent rows before applying any consolidation formulas.
Use the IF Function and Remove Duplicates Feature
This method works best when duplicate records are adjacent. It uses a simple IF formula to pull the second email into the first row, followed by cleaning up the duplicates.
Select your dataset and go to Data > Sort to organize records by the student identifier column (e.g., Column A) so duplicates are grouped together.
In an empty column next to the primary email, enter =IF(A2=A3,C3,"") (assuming Column A is the ID and Column C is the email) and press Enter.
Click the bottom-right corner of the cell containing the formula and drag the fill handle down to apply it to the rest of the column.
Select the newly calculated column, copy it (Ctrl+C), right-click the same selection, and choose Paste Special > Values to lock the data.
Select your entire data range, go to the Data tab > Remove Duplicates, check the student identifier column only, and click OK to delete the redundant rows.
Consolidate Data Using Advanced Array Formulas (Office 365)
For advanced users with access to modern dynamic arrays, this complex formula extracts and reshapes data without needing manual deletion.
Consolidate Duplicate Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data tools, including advanced formulas and a one-click 'Remove Duplicates' feature, to help you clean up and consolidate contact lists seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your duplicated student records.
- 2. Sort and apply formula: Sort by the student ID. In a new column, use the =IF(A2=A3,C3,"") formula to shift the secondary email up.
- 3. Paste as values: Copy the new column, click the 'Paste' dropdown in the Home tab, and select 'Paste as Values'.
- 4. Remove duplicates: Navigate to the Data tab, click 'Remove Duplicates', select your identifier column, and apply.
Frequently Asked Questions
Why did my IF formula return an error or unexpected result?
This typically happens if the data is not sorted first. The formula =IF(A2=A3,C3,"") relies on duplicate records being exactly adjacent to each other. Ensure you sort your data by the student identifier before applying the formula.
How can I merge more than two email addresses per student?
If students have three or more emails, you can use a combination of the TEXTJOIN function and IF arrays (e.g., =TEXTJOIN(", ", TRUE, IF(A$2:A$10=A2, C$2:C$10, ""))). Alternatively, use the Power Query tool to group by student ID and combine the text.
Do I have to convert the formula to values before removing duplicates?
Yes. If you skip the Paste Special > Values step, removing duplicate rows will shift the cell references. This will cause the remaining formulas to calculate incorrectly or display a #REF! error.
Can I use the 'Consolidate' tool for text data like emails?
No, the built-in Data > Consolidate tool is designed primarily for summarizing numerical data (like calculating sums or averages) and cannot be used to concatenate text strings such as email addresses.
